Java程序都是通过JDBC连接数据库,JDBC是由SUN公司提出的一系列规范,但是它之定义了接口规范,而具体的实现由各个数据库产商来实现。JDBC是一种典型的桥接模式。
(1)后台数据库
CREATE TABLE `role` (
`id` bigint(20) NOT NULL,
`role_name` varchar(255) DEFAULT NULL,
`note` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `role` VALUES ('1', '老师', '教书育人');
COMMIT;
(2)pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.qhc</groupId>
<artifactId>jdbc-example</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
</dependencies>
</project>
(3)代码
package com.qhc.jdbc;
/**
* 实体类-role
*/
public class Role {
private Long id;
private String roleName;
private String note;
//getter AND setter
}
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcExample {
private static String DRIVER = "com.mysql.jdbc.Driver";
private static String URL = "jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true";
String USERNAME = "root";
String PASSWORD = "123456";
/**
* (1)连接数据库,注册驱动和数据库信息
* (2)操作Connection,打开Statement对象
* (3)通过Statement执行SQL,返回结果到ResultSet对象
* (4)使用ResultSet读取数据,然后通过代码转化为具体的POJO对象
* (5)关闭数据库相关资源
*/
public static void main(String[] args) {
JdbcExample example = new JdbcExample();
Role role = example.getRole(1L);
System.out.println("角色名称为:" + role.getRoleName());
}
//获取连接
private Connection getConnection() {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (ClassNotFoundException e1) {
Logger.getLogger(JdbcExample.class.getName()).log(Level.SEVERE,null,e1);
return null;
} catch (SQLException e2) {
Logger.getLogger(JdbcExample.class.getName()).log(Level.SEVERE,null,e2);
return null;
}
return connection;
}
//获取指定id的角色信息
public Role getRole(Long id) {
Connection connection = getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = connection.prepareStatement("select id,role_name, note from role where id = ?");
ps.setLong(1,id); //设置占位1处的参数
rs = ps.executeQuery(); //执行查询
while (rs.next()) {
Long roleId = rs.getLong("id");
String userName = rs.getString("role_name");
String note = rs.getString("note");
Role role = new Role();
role.setId(roleId);
role.setRoleName(userName);
role.setNote(note);
return role;
}
} catch (SQLException e) {
Logger.getLogger(JdbcExample.class.getName()).log(Level.SEVERE,null,e);
} finally {
close(rs,ps,connection);
}
return null;
}
//关闭连接
private void close(ResultSet rs, Statement stmt, Connection connection) {
try {
if (rs!=null && !rs.isClosed()) {
rs.close();
}
if (stmt!=null && !stmt.isClosed()) {
stmt.close();
}
if (connection!=null && !connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
Logger.getLogger(JdbcExample.class.getName()).log(Level.SEVERE,null,e);
}
}
}
参考资料:深入浅出的MyBatis技术原理与实战