Common JDBC APIs, jdbcapi

Common JDBC APIs, jdbcapi

There are three ways to create a database connection:

package com.victor_01;import java.sql.Connection;import java.sql.Driver;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;import org.junit.Test;public class Test1 {private String url = "jdbc:mysql://192.168.244.144:3306/test";private String user = "root";private String password = "123456";@Testpublic void test1() throws Exception {Driver driver = new com.mysql.jdbc.Driver();Properties prop = new Properties();prop.setProperty("user", user);prop.setProperty("password", password);Connection conn = driver.connect(url, prop);System.out.println(conn);}@Testpublic void test2() throws SQLException{Driver driver = new com.mysql.jdbc.Driver();DriverManager.registerDriver(driver);Connection conn = DriverManager.getConnection(url, user, password);System.out.println(conn);}@Testpublic void test3() throws Exception {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection(url, user, password);System.out.println(conn);}}

The third type is recommended. In fact, the second and third types are essentially one type, and the latter is the optimized version of the former.

 

Core APIs of JDBC Interfaces

Java. SQL. * And javax. SQL. * |-Driver interfaces are the core APIs of the JDBC interface. All specific database vendors must implement this interface. |-Connect (url, properties): Method for connecting to the database. Url: URL to connect to the Database URL Syntax: jdbc Protocol: Database sub-Protocol: // host: Port/Database user: Database user Name password: Database user password |-DriverManager class: driver manager class, used to manage all registered drivers |-registerDriver (driver): registers a driver Class Object |-Connection getConnection (url, user, password ); obtain the Connection object |-Connection interface: the Connection object between the java program and the database. |-Statement createStatement (): Create a Statement object |-PreparedStatement prepareStatement (String SQL) create a PreparedStatement object |-CallableStatement prepareCall (String SQL) create a CallableStatement object |-Statement interface: execute static SQL statements |-int executeUpdate (String SQL): Execute static update SQL statements (DDL, DML) |-ResultSet executeQuery (String SQL ): executed static query SQL statement (DQL) |-PreparedStatement interface: used to execute pre-compiled SQL statements |-int executeUpdate (): Run pre-compiled update SQL statements (DDL, DML) |-ResultSet executeQuery (): Execute the pre-compiled query SQL statement (DQL) |-CallableStatement interface: call xxx |-ResultSet executeQuery (): call the Stored Procedure Method |-ResultSet interface: Used to encapsulate the queried data |-boolean next (): move the cursor to the next row |-getXX (): Get the column Value

 

Statement Interface

package com.victor_01;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import org.junit.Test;public class Demo2 {private String url = "jdbc:mysql://192.168.244.144:3306/test";private String user = "root";private String password = "123456";@Testpublic void Test1() {Connection conn = null;Statement stmt = null;ResultSet rs = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection(url, user, password);stmt = conn.createStatement();String sql = "drop table if exists jdbc_test";int result = stmt.executeUpdate(sql);System.out.println("Drop table:" + result);sql = "create table jdbc_test(id int,name varchar(10))";result = stmt.executeUpdate(sql);System.out.println("Create result:" + result);sql = "insert into jdbc_test values(1,'hello')";result = stmt.executeUpdate(sql);System.out.println("Insert result:" + result);int id = 2;String name = "world";sql = "insert into jdbc_test values(" + id + ",'" + name + "')";result = stmt.executeUpdate(sql);System.out.println("Insert result2:" + result);name = "java";sql = "update jdbc_test set name='" + name + "' where id=" + id+ "";result = stmt.executeUpdate(sql);System.out.println("Update result:" + result);sql = "select * from jdbc_test";rs = stmt.executeQuery(sql);while (rs.next()) {System.out.println(rs.getInt("id") + " " + rs.getString("name"));}} catch (Exception e) {e.printStackTrace();} finally {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (stmt != null)try {stmt.close();} catch (SQLException e) {e.printStackTrace();}if (conn != null)try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

 

PreparedStatement Interface

package com.victor_01;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import org.junit.Test;public class Demo3 {private String url="jdbc:mysql://192.168.244.144:3306/test";private String user="root";private String password="123456";@Testpublic void PreparedStatementTest() throws Exception{   Class.forName("com.mysql.jdbc.Driver");   Connection conn=DriverManager.getConnection(url, user, password);      String sql="insert into jdbc_test(id,name) values(?,?)";   PreparedStatement prestmt =conn.prepareStatement(sql);   prestmt.setInt(1, 3);   prestmt.setString(2,"tom");   int result=prestmt.executeUpdate();   System.out.println(result);      sql="update jdbc_test set name=? where id=?";   prestmt=conn.prepareStatement(sql);   prestmt.setString(1, "steve");   prestmt.setInt(2, 3);   result=prestmt.executeUpdate();   System.out.println(result);      sql="select * from jdbc_test where id=?";   prestmt=conn.prepareStatement(sql);   prestmt.setInt(1, 3);   ResultSet rs= prestmt.executeQuery();   while(rs.next()){   System.out.println("id="+rs.getInt(1)+";name="+rs.getString(2));   }   rs.close();   prestmt.close();   conn.close();   }   }

 

CallableStatement Interface

Package com. performance_01; import java. SQL. callableStatement; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. statement; import org. junit. test; public class Demo3 {private String url = "jdbc: mysql: // 192.168.244.144: 3306/test"; private String user = "root"; private String password = "123456 "; @ Testpublic void PreparedStatementTest () throws Exception {Class. forName (" Com. mysql. jdbc. driver "); Connection conn = DriverManager. getConnection (url, user, password); String SQL = "drop procedure findById"; Statement stmt = conn. createStatement (); int result1_stmt.exe cuteUpdate (SQL); System. out. println ("drop result: =" + result); SQL = "CREATE PROCEDURE findById (IN sid INT) BEGIN select * from jdbc_test where id = sid; end"; stmt = conn. createStatement (); result1_stmt.exe cuteUpdate (SQL ); System. out. println ("create result: =" + result); // directly call the Stored Procedure SQL = "call findById (2)"; CallableStatement stmt1 = conn. prepareCall (SQL); ResultSet rs1_stmt1.exe cuteQuery (); while (rs. next () {System. out. println ("id =" + rs. getInt (1) + ", name =" + rs. getString (2);} // call the Stored Procedure SQL = "call findById (?) "; Stmt1 = conn. prepareCall (SQL); stmt1.setInt (1, 4); rs1_stmt1.exe cuteQuery (); while (rs. next () {System. out. println ("id =" + rs. getInt (1) + ", name =" + rs. getString (2);} // Stored procedure SQL = "drop procedure findById1"; stmt = conn. createStatement (); resultimplements stmt.exe cuteUpdate (SQL); System. out. println ("drop findById1 result:" + result); SQL = "CREATE PROCEDURE findById1 (IN sid INT, OUT sname VARCHAR (10) BEGIN select Name into sname from jdbc_test where id = sid; end "; stmt = conn. createStatement (); resultimplements stmt.exe cuteUpdate (SQL); System. out. println ("create result: =" + result); SQL = "call findById1 (?,?) "; Stmt1 = conn. prepareCall (SQL); stmt1.setInt (1, 4); stmt1.registerOutParameter (2, java. SQL. types. VARCHAR); stmt1.executeQuery (); // Note: The result is not returned to the ResultSet, but to the output parameter. String sname = stmt1.getString (2); System. out. println (sname); rs. close (); stmt1.close (); stmt. close (); conn. close ();}}