Transcript 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
• 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”);
Username
Password
Step 3) Create Statement and Execute
SQL Query
Statement stmt = conn.createStatement();
ResultSetrs = stmt.executeQuery(
"SELECT * FROM classes");
Step 4) Iterate over Results
while (rs.next()){
System.out.println(rs.getString("prof"));
}
• Imagine result set is a “table” or grid with
each column having a name
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)