你的位置:首页 > Java教程

[Java教程]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();	  }	  			}