Java Database Connection: JDBC and MySQL

Java Database Connection: JDBC and MySQL

JDBC - standard API allow to connect with MySQL

Java Application <--------- >JDBC <---------> Database

JDBC Architecture

• JDBC Driver 
• JDBC Driver Implementation
• JDBC Driver Manager

JDBC API is definded in following packages:

• java.sql and javax.sql

------------------------

Submitting SQL Queries  - JAVA

1. Get a connection to database
2. Create Statement object
3. Execute SQL query
4. Process Result Set

Get a connection to database

                   import java.sql.*;
                    ....
                   String dbUrl = "jdbc:mysql://localhost:3306/demo?                                                                                  autoReconnect=true&useSSL=false";
                    String user = "root";
                    String pass = "Sasanka01";
                     ....
                     Connection myConn = DriverManager.getConnection(dbUrl, user, pass);

Create Statement object 

Statement myStmt = myConn.createStatement()

Execute SQL query

                       ResultsSet myRs = myStmt.executeQuery("select * from employees");

Process Result Set

• Result Set is initially placed before first row
• Method: ResultSet.next()
• moves forward one row
• returns true if there are more rows to proces
• Collection of methods for reading data
• getXXX(columnName)
• getXXX(columnIndex) one -based

while (myRs.next()) {
System.out.println(myRs.getString("last_name") + ", " + myRs.getString("first_name"));
}