JDBC—MeteData、PreparedStatement、CallableStatement

4. MetaData

4.1 结果集的MetaData

ResultSet的getMetaData()方法可以返回结果集元数据对象ResultSetMetaData。

package com.amaker.test;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import com.amaker.util.DBUtil;public class MetaDataTest {public static void main(String[] args) {// testDataBaseMetaData();testResultSetMetaData();}static void testResultSetMetaData() {DBUtil util = new DBUtil();Connection conn = util.openConnection();String sql = "select id,name,age from StuTbl";try {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(sql);ResultSetMetaData metadata = rs.getMetaData();int count = metadata.getColumnCount();for (int i = 1; i <= count; i++) {String name = metadata.getColumnName(i);System.out.println(name);}} catch (SQLException e) {e.printStackTrace();}}static void testDataBaseMetaData() {DBUtil util = new DBUtil();Connection conn = util.openConnection();try {DatabaseMetaData metadata = conn.getMetaData();System.out.println("MajorVersion:"+ metadata.getDatabaseMajorVersion());System.out.println("MinorVersion:"+ metadata.getDatabaseMinorVersion());System.out.println("name:" + metadata.getDatabaseProductName());System.out.println(metadata.getDatabaseProductVersion());} catch (SQLException e) {e.printStackTrace();}}}

运行结果：

4.2 数据库的MetaData

通过数据库连接的getMetaData()方法可以获得数据库连接的对象实例DataBaseMetaData。

package com.amaker.test;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.SQLException;import com.amaker.util.DBUtil;public class MetaDataTest {public static void main(String[] args) {testDataBaseMetaData();}static void testDataBaseMetaData() {DBUtil util = new DBUtil();Connection conn = util.openConnection();try {DatabaseMetaData metadata = conn.getMetaData();System.out.println("MajorVersion:"+ metadata.getDatabaseMajorVersion());System.out.println("MinorVersion:"+ metadata.getDatabaseMinorVersion());System.out.println("name:" + metadata.getDatabaseProductName());System.out.println(metadata.getDatabaseProductVersion());} catch (SQLException e) {e.printStackTrace();}}}

运行结果：

5. PreparedStatement

5.1 PreparedStatement简介

 PreparedStatement表示预编译的 SQL 语句的对象。SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。

5.2 为占位符“?”赋值

使用Connection的prepareStatement()方法得到PreparedStatement对象，通过SetXxx()来对占位符“？”进行赋值。

例如：

package com.amaker.test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import com.amaker.util.DBUtil;public class PreparedStatementTest {public static void main(String[] args) {getPreparedStatement();}static void getPreparedStatement() {DBUtil util = new DBUtil();Connection conn = util.openConnection();String sql = "select id,name,age from StuTbl where id=?";try {PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setInt(1, 1);System.out.println(pstmt);} catch (SQLException e) {e.printStackTrace();}}}

运行测试：

5.3 使用PreparedStatement动态执行SQL语句

使用PreparedStatement进行增删改查：

进行增加、删除、修改时使用executeUpdate()方法来执行SQL语句。

先查询StuTbl表中的数据：

mysql> select * from StuTbl;+----+--------+------+| id | name   | age  |+----+--------+------+|  1 | tom    |   18 ||  2 | bigtom |   30 ||  3 | kite   |   20 |+----+--------+------+3 rows in set (0.00 sec)

编写程序：

package com.amaker.test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import com.amaker.util.DBUtil;public class PreparedStatementTest {public static void main(String[] args) {// getPreparedStatement();// add("kite", 30);Student s = new Student();s.setId(4);s.setName("jerry");s.setAge(23);add(s);}static void add(Student s) {DBUtil util = new DBUtil();Connection conn = util.openConnection();String sql = "insert into StuTbl (id,name,age) values (?,?,?)";try {PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setInt(1, s.getId());pstmt.setString(2, s.getName());pstmt.setInt(3, s.getAge());pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {util.closeConnection(conn);}}static void add(int id, String name, int age) {DBUtil util = new DBUtil();Connection conn = util.openConnection();String sql = "insert into StuTbl (id,name,age) values (?,?,?)";try {PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setInt(1, id);pstmt.setString(2, name);pstmt.setInt(3, age);pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {util.closeConnection(conn);}}}class Student {private int id;private String name;private int age;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}}

查看结果：

mysql> select * from StuTbl;+----+--------+------+| id | name   | age  |+----+--------+------+|  1 | tom    |   18 ||  2 | bigtom |   30 ||  3 | kite   |   20 ||  4 | jerry  |   23 |+----+--------+------+4 rows in set (0.00 sec)

进行查询时使用executeQuery()方法来执行查询的SQL语句。

package com.amaker.test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import com.amaker.util.DBUtil;public class PreparedStatementTest {public static void main(String[] args) {// getPreparedStatement();// add("kite", 30);// Student s = new Student();// s.setId(4);// s.setName("jerry");// s.setAge(23);// add(s);query();}static void query() {DBUtil util = new DBUtil();Connection conn = util.openConnection();String sql = "select id,name,age from StuTbl where age > ?";try {PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setInt(1, 20);ResultSet rs = pstmt.executeQuery();while (rs.next()) {int id = rs.getInt(1);String name = rs.getString(2);int age = rs.getInt(3);System.out.println(id + ":" + name + ":" + age);}} catch (SQLException e) {e.printStackTrace();} finally {util.closeConnection(conn);}}}

运行结果：

6.  CallableStatement

6.1 CallableStatement简介

用于执行 SQL 存储过程的接口。JDBC API 提供了一个存储过程 SQL 转义语法，该语法允许对所有 RDBMS 使用标准方式调用存储过程。

6.2  调用简单的存储过程

先创建一个简单的存储过程：

mysql> create procedure all_stu() select * from StuTbl;Query OK, 0 rows affected (0.17 sec)mysql> call all_stu();+----+--------+------+| id | name   | age  |+----+--------+------+|  1 | tom    |   18 ||  2 | bigtom |   30 ||  3 | kite   |   20 ||  4 | jerry  |   23 |+----+--------+------+4 rows in set (0.05 sec)Query OK, 0 rows affected (0.05 sec)

编写Java程序调用该存储过程：

package com.amaker.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import com.amaker.util.DBUtil;public class CallableStatementTest {public static void main(String[] args) {testCallableStatement();}static void testCallableStatement() {DBUtil util = new DBUtil();Connection conn = util.openConnection();// 调用存储过程的SQL语句的写法需要加个大括号{}String sql = "{call all_stu()}";try {CallableStatement cstmt = conn.prepareCall(sql);ResultSet rs = cstmt.executeQuery();while (rs.next()) {int id = rs.getInt(1);String name = rs.getString(2);int age = rs.getInt(3);System.out.println(id + ":" + name + ":" + age);}} catch (SQLException e) {e.printStackTrace();}}}

运行结果：

6.3 调用有输入参数的存储过程

创建一个带有输入参数的存储过程：

mysql> create procedure insert_stu(in n varchar(20),in a int)    -> insert into StuTbl (name,age) values (n,a);Query OK, 0 rows affected (0.00 sec)mysql> call insert_stu("joe",30);Query OK, 1 row affected (0.00 sec)mysql> select * from StuTbl;+----+--------+------+| id | name   | age  |+----+--------+------+|  1 | tom    |   18 ||  2 | bigtom |   30 ||  3 | kite   |   20 ||  4 | jerry  |   23 ||  0 | joe    |   30 |+----+--------+------+5 rows in set (0.00 sec)

mysql> delete from StuTbl where id = 0;Query OK, 1 row affected (0.00 sec)

编写Java程序调用该存储过程：

package com.amaker.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import com.amaker.util.DBUtil;public class CallableStatementTest {public static void main(String[] args) {// testCallableStatement();testCallableStatement2();}static void testCallableStatement2() {DBUtil util = new DBUtil();Connection conn = util.openConnection();// 调用存储过程的SQL语句的写法需要加个大括号{}String sql = "{call insert_stu(?,?)}";try {CallableStatement cstmt = conn.prepareCall(sql);cstmt.setString(1, "zhangsan");cstmt.setInt(2, 23);cstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();}}static void testCallableStatement() {DBUtil util = new DBUtil();Connection conn = util.openConnection();// 调用存储过程的SQL语句的写法需要加个大括号{}String sql = "{call all_stu()}";try {CallableStatement cstmt = conn.prepareCall(sql);ResultSet rs = cstmt.executeQuery();while (rs.next()) {int id = rs.getInt(1);String name = rs.getString(2);int age = rs.getInt(3);System.out.println(id + ":" + name + ":" + age);}} catch (SQLException e) {e.printStackTrace();}}}

测试结果：

mysql> select * from StuTbl;+----+----------+------+| id | name     | age  |+----+----------+------+|  1 | tom      |   18 ||  2 | bigtom   |   30 ||  3 | kite     |   20 ||  4 | jerry    |   23 ||  0 | zhangsan |   23 |+----+----------+------+5 rows in set (0.00 sec)

6.4 调用有输入、输出参数的存储过程