180 likes | 428 Views
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.
E N D
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 • 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
Overview Java-based Application MySQL Connector/J MySQL DBMS
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
JDBC • Some Important JDBC API classes: • Driver Manager • Connection • Statement • ResultSet • SQLException
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.
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
Step 1) Load the MySQL JDBC Driver Class.forName("com.mysql.jdbc.Driver");
Step 2) Create a Connection Server Database Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost/cse3330a?”+ “user=root&password=aSecret123”); Password Username
Step 3) Create Statement and Execute SQL Query Statement stmt = conn.createStatement(); ResultSetrs = stmt.executeQuery( "SELECT * FROM classes");
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")); }
Step 5) Clean Up rs.close(); stmt.close(); conn.close(); Note – Reverse Order
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)