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)