要素一：Driver接口实现类
要素二：URL
要素三：用户名
要素四：密码
二. JDBC_Utils 类用于连接和关闭数据库1. 声明配置文件：在工程的 src 下新建一个名为 jdbc.properties 的 File ，写入四要素
user=root password=zxcvbnm5237 url=jdbc:mysql://localhost:3306/test driverClass=com.mysql.jdbc.Driver
2. 将连接和关闭数据库的操作用 JDBC_Utils 类保存
package util;
public class JDBC_Utils {
//getConnection类用于连接数据库
public static Connection getConnection() throws Exception {
//1.通过读取配置文件获取连接
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//2.使用反射加载驱动
Class.forName(driverClass);
//3.使用DriverManager.getConnection()方法获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
//closeResource类用于关闭数据库
public static void closeResource(Connection conn, Statement ps, ResultSet rs) {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
三. DAO（Data Access Object）封装通用的CRUD操作
1. 先将表中所有字段封装在包 bean 中（以Customer为例）
package bean;
public class Customer{
// Customer表的四个字段
private int id;
private String name;
private String email;
private Date birth;
//ALT+SHIFT+s
// Generate Constructor using Fields
// Generate Getters and Setters
// Generate toString()
public Customer() {
super();
}
public Customer(int id, String name, String email, Date birth) {
super();
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
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 String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
@Override
public String toString() {
return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
}
}
2. baseDAO封装所有数据表的通用的操作
public abstract class baseDAO3. 创建DAO接口用于规范对具体表的常用操作（以Customer为例）{ private Class clazz = null; { //获取当前baseDAO的子类继承的父类中的泛型 Type genericSuperclass = this.getClass().getGenericSuperclass(); ParameterizedType paramType = (ParameterizedType) genericSuperclass; Type[] typeArguments = paramType.getActualTypeArguments();//获取了父类的泛型参数 clazz = (Class ) typeArguments[0];//泛型的第一个参数 } // update通用的增删改操作（考虑事务） public int update(Connection conn, String sql, Object... args) { PreparedStatement ps = null; try { // 1.预编译sql语句，返回PreparedStatement的实例 ps = conn.prepareStatement(sql); // 2.填充占位符 for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } // 3.执行 return ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { // 4.资源的关闭 JDBC_Utils.closeResource(null, ps); } return 0; } // getInstance查询数据表中的一条记录（考虑事务） public T getInstance(Connection conn, String sql, Object... args) { PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } rs = ps.executeQuery(); // 获取结果集的元数据 :ResultSetmetaData ResultSetmetaData rsmd = rs.getmetaData(); // 通过ResultSetmetaData获取结果集中的列数 int columnCount = rsmd.getColumnCount(); if (rs.next()) { T t = clazz.newInstance(); // 处理结果集一行数据中的每一个列 for (int i = 0; i < columnCount; i++) { // 获取列值 Object columValue = rs.getObject(i + 1); // 获取每个列的列名 String columnLabel = rsmd.getColumnLabel(i + 1); // 通过反射给t对象指定的columnName属性赋值为columValue Field field = clazz.getDeclaredField(columnLabel); field.setAccessible(true); field.set(t, columValue); } return t; } } catch (Exception e) { e.printStackTrace(); } finally { JDBC_Utils.closeResource(null, ps, rs); } return null; } // getForList查询数据表中的多条记录构成的集合（考虑事务） public List getForList(Connection conn, String sql, Object... args) { PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } rs = ps.executeQuery(); ResultSetmetaData rsmd = rs.getmetaData(); int columnCount = rsmd.getColumnCount(); ArrayList list = new ArrayList (); // 创建集合对象 while (rs.next()) { T t = clazz.newInstance(); for (int i = 0; i < columnCount; i++) { Object columValue = rs.getObject(i + 1); String columnLabel = rsmd.getColumnLabel(i + 1); Field field = clazz.getDeclaredField(columnLabel); field.setAccessible(true); field.set(t, columValue); } list.add(t); } return list; } catch (Exception e) { e.printStackTrace(); } finally { JDBC_Utils.closeResource(null, ps, rs); } return null; } //getValue查询特殊值（查询某字段中的最大值，平均值等） public E getValue(Connection conn,String sql,Object...args){ PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); for(int i = 0;i < args.length;i++){ ps.setObject(i + 1, args[i]); } rs = ps.executeQuery(); if(rs.next()){ return (E) rs.getObject(1); } } catch (SQLException e) { e.printStackTrace(); }finally{ JDBCU_tils.closeResource(null, ps, rs); } return null; } }
public interface CustomerDAO {
//将cust对象插入到数据库中（增）
void insert(Connection conn,Customer cust);
//针对指定的id，删除表中的一条记录（删）
void deleteById(Connection conn,int id);
//针对内存中的cust对象，去修改表中指定的记录（改）
void update(Connection conn,Customer cust);
// 针对指定的id查询得到表中的一条记录（查）
Customer getCustomerById(Connection conn,int id);
//查询表中的所有记录构成的集合
List getAll(Connection conn);
//返回数据表中的数据的条目数
Long getCount(Connection conn);
//返回数据表中最大的生日
Date getMaxBirth(Connection conn);
}
4.Customer的具体实现类继承了baseDAO以及实现了CustomerDAO接口
public class CustomerDAOImpl extends baseDAODruid数据库连接池（实际开发） 一. 获取数据库连接implements CustomerDAO{ public void insert(Connection conn, Customer cust) { String sql = "insert into customers(name,email,birth)values(?,?,?)"; update(conn, sql,cust.getName(),cust.getEmail(),cust.getBirth()); } public void deleteById(Connection conn, int id) { String sql = "delete from customers where id = ?"; update(conn, sql, id); } public void update(Connection conn, Customer cust) { String sql = "update customers set name = ?,email = ?,birth = ? where id = ?"; update(conn, sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId()); } public Customer getCustomerById(Connection conn, int id) { String sql = "select id,name,email,birth from customers where id = ?"; Customer customer = getInstance(conn, sql,id); return customer; } public List getAll(Connection conn) { String sql = "select id,name,email,birth from customers"; List list = getForList(conn, sql); return list; } public Long getCount(Connection conn) { String sql = "select count(*) from customers"; return getValue(conn, sql); } public Date getMaxBirth(Connection conn) { String sql = "select max(birth) from customers"; return getValue(conn, sql); } }
1. 将数据库连接池驱动 jar 包拷贝到 Java 工程的 lib 文件夹下。
2. 在驱动jar上右键–>Build Path–>Add to Build Path
1. 在工程的 src 下新建一个名为druid.properties 的 File ，写入六要素
url=jdbc:mysql:///test username=root password=zxcvbnm5237 driverClassName=com.mysql.jdbc.Driver initialSize=8 maxActive=10
2. 将连接和关闭数据库的操作用 JDBC_Utils 类保存
public class JDBC_Utils {
//获取数据库的连接
public static Connection getConnection() throws Exception {
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
Class.forName(driverClass);
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
//使用Druid数据库连接池技术
private static DataSource source;
static{
try {
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
source = DruidDataSourceFactory.createDataSource(pros);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException{
Connection conn = source.getConnection();
return conn;
}
//使用dbutils.jar中提供的DbUtils工具类，关闭资源
public static void closeResource(Connection conn,Statement ps,ResultSet rs){
DbUtils.closeQuietly(conn);
DbUtils.closeQuietly(ps);
DbUtils.closeQuietly(rs);
}
}
三. Apache-DBUtils 封装通用的CRUD操作
commons-dbutils 是 Apache 组织提供的一个开源 JDBC 工具类库 , 封装了针对于数据库的增删改查操作
1. 将 DBUtils 驱动 jar 包拷贝到 Java 工程的 lib 文件夹下。
2. 在驱动 jar上右键–>Build Path–>Add to Build Path
3. 使用 QueryRunner 测试 CRUD
public class QueryRunnerTest {
//update测试增删改
@Test
public void testInsert() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBC_Utils.getConnection();
String sql = "insert into customers(name,email,birth)values(?,?,?)";
//可以返回影响（增删改）的记录数量
int insertCount = runner.update(conn, sql, "蔡徐坤","caixukun@163.com","1997-08-02");
System.out.println("添加了" + insertCount + "条记录");
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBC_Utils.closeResource(conn, null);
}
}
//query测试查询表中一条记录
@Test
public void testQuery1(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBC_Utils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
//BeanHander是ResultSetHandler接口的实现类，用于封装表中的一条记录。
BeanHandler handler = new BeanHandler<>(Customer.class);
Customer customer = runner.query(conn, sql, handler, 23);
System.out.println(customer);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBC_Utils.closeResource(conn, null);
}
}
//query测试查询表中多条记录
@Test
public void testQuery2() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBC_Utils.getConnection();
String sql = "select id,name,email,birth from customers where id < ?";
//BeanListHandler是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合。
BeanListHandler handler = new BeanListHandler<>(Customer.class);
List list = runner.query(conn, sql, handler, 23);
list.forEach(System.out::println);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBC_Utils.closeResource(conn, null);
}
}
//query测试查询表中一条记录，该记录的字段及相应字段值作为map中的key和value返回
@Test
public void testQuery3(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBC_Utils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
//MapHander是ResultSetHandler接口的实现类，对应表中的一条记录
MapHandler handler = new MapHandler();
Map map = runner.query(conn, sql, handler, 23);
System.out.println(map);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBC_Utils.closeResource(conn, null);
}
}
//query测试查询表中多条记录，多条记录的字段及相应字段的值作为map中的key和value，并将这些map添加到List中
@Test
public void testQuery4(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBC_Utils.getConnection();
String sql = "select id,name,email,birth from customers where id < ?";
// MapListHander是ResultSetHandler接口的实现类，对应表中的多条记录。
MapListHandler handler = new MapListHandler();
List> list = runner.query(conn, sql, handler, 23);
list.forEach(System.out::println);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBC_Utils.closeResource(conn, null);
}
}
//query查询特殊值
@Test
public void testQuery5(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBC_Utils.getConnection();
String sql = "select count(*) from customers";
//ScalarHandler用于查询特殊值
ScalarHandler handler = new ScalarHandler();
Long count = (Long) runner.query(conn, sql, handler);
System.out.println(count);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBC_Utils.closeResource(conn, null);
}
}
//query查询特殊值
@Test
public void testQuery5(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBC_Utils.getConnection();
String sql = "select count(*) from customers";
//ScalarHandler用于查询特殊值
ScalarHandler handler = new ScalarHandler();
Long count = (Long) runner.query(conn, sql, handler);
System.out.println(count);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBC_Utils.closeResource(conn, null);
}
}
}
