JDBC是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如更新数据库中数据的方法。
本文开始之前先准备一个数据 test,新建一个 student 表并插入数据
CREATE TABLE `student` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`course` varchar(10) NOT NULL,
`grade` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES ('1', '张三', '语文', '74');
INSERT INTO `student` VALUES ('2', '张三', '数学', '83');
INSERT INTO `student` VALUES ('3', '张三', '物理', '93');
INSERT INTO `student` VALUES ('4', '李四', '语文', '79');
INSERT INTO `student` VALUES ('5', '李四', '数学', '86');
INSERT INTO `student` VALUES ('6', '李四', '物理', '88');
JDBC 的使用步骤
- 注册驱动
- 建立连接
- 创建运行 sql 语句
- 运行语句
- 处理运行结果
- 释放资源
JDBC最基础的写法
public class Test {
private static final String driver = "com.mysql.jdbc.Driver";
private static final String dbUrl = "jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
private static final String username = "root";
private static final String password = "root";
@org.junit.Test
public void test(){
Connection connection = null;
Statement stmt = null;
ResultSet resultSet = null;
try {
//1.注册驱动
Class.forName(driver);
//2.建立连接
connection = DriverManager.getConnection(dbUrl, username, password);
//3.创建sql语句
String sql = "select * from student where 1";
stmt = connection.createStatement();
//4.运行语句
resultSet = stmt.executeQuery(sql);
//5.处理结果
while (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String course = resultSet.getString("course");
int grade = resultSet.getInt("grade");
System.out.printf("id => %d, name => %s, course => %s, grade => %d",id, name, course, grade);
System.out.println();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//6.释放资源
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
// 运行结果
id => 1, name => 张三, course => 语文, grade => 74
id => 2, name => 张三, course => 数学, grade => 83
id => 3, name => 张三, course => 物理, grade => 93
id => 4, name => 李四, course => 语文, grade => 79
id => 5, name => 李四, course => 数学, grade => 86
id => 6, name => 李四, course => 物理, grade => 88
对上边这个类进行优化,将数据库用户名和密码、数据库驱动等信息提取出来放在一个db.properties 文件中。statement 也改为使用更加安全的 prepareStatement。
db.properties 文件如下
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
username=root
password=root
Java 代码
public class Test_02 {
@Test
public void test() throws Exception {
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(is);
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, username, password);
String sql = "select * from student where id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, 3);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String course = resultSet.getString("course");
int grade = resultSet.getInt("grade");
System.out.printf("id => %d, name => %s, course => %s, grade => %d",id, name, course, grade);
System.out.println();
}
resultSet.close();
preparedStatement.close();
connection.close();
}
}
// 运行结果
id => 3, name => 张三, course => 物理, grade => 93
创建一个公共的增、删、改方法
/**
* 通用的sql操作方法,除了查询操作之外
* @param sql
* @param args
*/
public void operate(String sql, Object ...args){
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JdbcUtil.getConnection();
ps = connection.prepareStatement(sql);
if(args.length > 0){
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.closeResources(connection, ps);
}
}
PrepareStatement 与 Statement 的区别
- Statement 用于执行静态SQL语句,在执行时,必须指定一个事先准备好的SQL语句;
- PrepareStatement 是预编译的SQL对象,sql语句被预编译并保存在对象中。被封装的sql语句代表某一类操作,语句中可以包含动态参数“?”,在执行时可以为“?”动态设置参数值;
- 使用 PrepareStatement 对象执行 sql 语句时,sql 被数据库进行解析和编译,然后被放到命令缓冲区,每当执行同一个 PrepareStatement 对象时,它就会被解析一次,但不会被再次编译。在缓冲区可以发现预编译的命令,并且可以重用;
- PrepareStatement 可以减少编译次数提高数据库性能。