1 / 14

MySQL , Java, and JDBC

MySQL , Java, and JDBC. CSE 3330 Southern Methodist University. Question. When’s the last time you pulled up Amazon.com and typed in an SQL Query directly? How about with Access.SMU ? Issued any SQL Queries directly recently? . Accessing MySQL.

miracle
Download Presentation

MySQL , Java, and JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MySQL, Java, and JDBC CSE 3330 Southern Methodist University

  2. Question • When’s the last time you pulled up Amazon.com and typed in an SQL Query directly? • How about with Access.SMU? Issued any SQL Queries directly recently?

  3. Accessing MySQL • We need a way to access from a programming languages • Various ways to do this in different languages • Java uses the JDBC API • JDBC = Java Database Connectivity • Allows standardized access to relational databases from Java

  4. Overview Java-based Application MySQL Connector/J MySQL DBMS

  5. JDBC • JDBC standardizes: • Initiating a connection to a RDBMS • Creating a SQL statement for later execution • Executing a SQL statement against the RDBMS • Retrieving the result of a query (if applicable) • MySQL’s Connector/J • Type 4 JDBC Driver • All Java/Native protocol driver

  6. JDBC • Some Important JDBC API classes: • Driver Manager • Connection • Statement • ResultSet • SQLException

  7. In your IDE • Create a new Project called JDBCTest • Add MySql JDBC Driver to your “libraries” that are used in compiling • In Netbeans, go to project properties, choose libraries on left, and select the aforementioned driver • Create an empty Java source file • import java.sql.*; • add a main method to the file.

  8. Overview public static void main (String [] args) { try { Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost/cse3330a?”+ “user=root&password=aSecret123" ); Statement stmt = conn.createStatement(); ResultSetrs = stmt.executeQuery("SELECT * FROM classes"); while (rs.next()){ System.out.println(rs.getString("prof")); } rs.close(); stmt.close(); conn.close(); } catch (Exception e) { System.out.println(e.getMessage()); } DOES NOT WORK; Get Main.java example From http:lyle.smu.edu/~rkotamarti/Main.java

  9. Step 1) Load the MySQL JDBC Driver Class.forName("com.mysql.jdbc.Driver");

  10. Step 2) Create a Connection Server Database Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost/cse3330a?”+ “user=root&password=aSecret123”); Password Username

  11. Step 3) Create Statement and Execute SQL Query Statement stmt = conn.createStatement(); ResultSetrs = stmt.executeQuery( "SELECT * FROM classes");

  12. Step 4) Iterate over Results • Imagine result set is a “table” or grid with each column having a name while (rs.next()){ System.out.println(rs.getString("prof")); }

  13. Step 5) Clean Up rs.close(); stmt.close(); conn.close(); Note – Reverse Order

  14. In-Class Exercise • Write a program that will: • Allow the user to enter their student id • Show a list of classes to the user (SELECT) • Allow the user to “register” for a class (INSERT) • Log in to MySQL directly to make sure the insert took place. • Don’t worry about details like double registering or overlapping times (esp. since we’re not storing times)

More Related