JDBC常用API小结
建立数据库链接的三种方式:
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"; @Test public 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); } @Test public 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); } @Test public void test3() throws Exception { Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection(url, user, password); System.out.println(conn); } }
推荐使用第三种,其实第二种和第三种本质上是一种,后者是前者的优化版。
JDBC接口核心的API
JDBC接口核心的API java.sql.* 和 javax.sql.* |- Driver接口: 表示java驱动程序接口。所有的具体的数据库厂商要来实现此接口。 |- connect(url, properties): 连接数据库的方法。 url: 连接数据库的URL URL语法: jdbc协议:数据库子协议://主机:端口/数据库 user: 数据库的用户名 password: 数据库用户密码 |- DriverManager类: 驱动管理器类,用于管理所有注册的驱动程序 |-registerDriver(driver) : 注册驱动类对象 |-Connection getConnection(url,user,password); 获取连接对象 |- Connection接口: 表示java程序和数据库的连接对象。 |- Statement createStatement() : 创建Statement对象 |- PreparedStatement prepareStatement(String sql) 创建PreparedStatement对象 |- CallableStatement prepareCall(String sql) 创建CallableStatement对象 |- Statement接口: 用于执行静态的sql语句 |- int executeUpdate(String sql) : 执行静态的更新sql语句(DDL,DML) |- ResultSet executeQuery(String sql) :执行的静态的查询sql语句(DQL) |-PreparedStatement接口:用于执行预编译sql语句 |- int executeUpdate() : 执行预编译的更新sql语句(DDL,DML) |-ResultSet executeQuery() : 执行预编译的查询sql语句(DQL) |-CallableStatement接口:用于执行存储过程的sql语句(call xxx) |-ResultSet executeQuery() : 调用存储过程的方法 |- ResultSet接口:用于封装查询出来的数据 |- boolean next() : 将光标移动到下一行 |-getXX() : 获取列的值
Statement接口
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"; @Test public 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接口
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"; @Test public 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接口
package com.victor_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"; @Test public 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 result=stmt.executeUpdate(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(); result=stmt.executeUpdate(sql); System.out.println("create result:="+result); //直接调用存储过程 sql="call findById(2)"; CallableStatement stmt1=conn.prepareCall(sql); ResultSet rs=stmt1.executeQuery(); while(rs.next()){ System.out.println("id="+rs.getInt(1)+",name="+rs.getString(2)); } //传参调用存储过程 sql="call findById(?)"; stmt1=conn.prepareCall(sql); stmt1.setInt(1, 4); rs=stmt1.executeQuery(); while(rs.next()){ System.out.println("id="+rs.getInt(1)+",name="+rs.getString(2)); } //带有输出参数的存储过程 sql="drop procedure findById1"; stmt=conn.createStatement(); result=stmt.executeUpdate(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(); result=stmt.executeUpdate(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(); //注意:结果不是返回到ResultSet中,而是返回到输出参数中。 String sname=stmt1.getString(2); System.out.println(sname); rs.close(); stmt1.close(); stmt.close(); conn.close(); } }