学习视频(Java300集):https://www.bilibili.com/video/BV1ct411n7oG
一、获取数据库连接的方式
- 基本步骤:
- 获取Driver实现类对象
- 提供要连接的数据库
- 提供连接需要的用户名和密码
- 获取连接
- 测试代码中使用
@Test
的方式,不用写main函数就可以运行单个类(class)
- 测试代码
- 方式一
package com.ljh.jdbc;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import org.junit.Test;
/*
测试数据库的连接
*/
public class ConnectionTest {
//方式一
@Test
public void testConnection1() throws SQLException {
//获取Driver的实现类对象
Driver driver = new com.mysql.jdbc.Driver();//com.mysql.jdbc.Driver为第三方api
/*
jdbc:mysql:协议
localhost:ip地址
3306:默认mysql的端口号
test:数据库名称
*/
String url = "jdbc:mysql://localhost:3306/test";
Properties info = new Properties();
//将用户名和密码封装在Properties中
info.setProperty("user","root");
info.setProperty("password","Myroot_ljh");
//用Connection的接口connect进行连接
Connection conn = driver.connect(url,info);
System.out.println(conn);
}
- 方式二:对方式一的迭代,程序中不存在第三方的api,使程序更具有可移植性
//方式二:对方式一的迭代,程序中不存在第三方的api,使程序更具有可移植性。
@Test
public void testConnection2() throws Exception {
//1,获取Driver实现类对象,使用反射(相当于方式一的Driver driver = new com.mysql.jdbc.Driver();)
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
//2,提供要连接的数据库
String url = "jdbc:mysql://localhost:3306/test";
//3,提供连接需要的用户名和密码
Properties info = new Properties();
info.setProperty("user","root");
info.setProperty("password","Myroot_ljh");
//4,获取连接
Connection conn = driver.connect(url,info);
System.out.println(conn);
}
- 方式三:对方式二的更改,用DriverManager替换Driver
//方式三:对方式二的更改,用DriverManager替换Driver
@Test
public void testConnection3() throws Exception {
//1,提供另外三个连接的基本信息
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "Myroot_ljh";
//2,获取Driver实现类的对象
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
//注册驱动
DriverManager.deregisterDriver(driver);
//3,获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
}
- 方式四:对方式三的优化,对第二步优化
//方式四:对方式三的优化,对第二步优化
@Test
public void testConnection4() throws Exception {
//1,提供另外三个连接的基本信息
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "Myroot_ljh";
//2,加载Driver
Class.forName("com.mysql.jdbc.Driver");//在MySQL中也可以省略,但不建议,因为不具有通用性
/*Driver driver = (Driver) clazz.newInstance();
//注册驱动
DriverManager.deregisterDriver(driver);
上述操作可以省略,因为在MySQL的Driver实现类中,已经声明了操作
*/
//3,获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
}
- 方式五:最终版,将数据库所需要连接的4个基本信息声明在配置文件中,通过读取文件的方式获取连接
//方式五:将数据库所需要连接的4个基本信息声明在配置文件中,通过读取文件的方式获取连接
/*
方式五的好处:
1,实现了代码与数据的分离,实现了解耦(解除耦合,或降低耦合度)
2,如果需要修改配置文件信息,可以避免程序重新打包
*/
@Test
public void testConnection5() throws Exception {
//1,读取配置文件中的4个基本信息
InputStream is = ConnectionTest.class.getClassLoader().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,加载驱动Driver
Class.forName(driverClass);
//3,获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
}
- 方式五中的配置文件:
- 测试结果:
二、实现对数据库的增删改
基本步骤
- 获取数据库的连接
- 预编译sql语句,获取PreparedStatement的实例
- 填充占位符(?)
- 执行
- 释放资源
- 测试代码
package com.ljh.jdbc;
import com.ljh.util.JDBCUtils;
import org.junit.Test;
import java.sql.*;
import java.text.ParseException;
/*
测试用PreparedStatement来替换Statement,实现对数据库的增删改操作
*/
public class PreparedStatementUpdateTest {
//测试通用的操作方法
@Test
public void testCommonUpdate() throws ParseException {
/*//1,增加一条记录
String sql1 = "insert into students(name,sex,age,email,birth)value (?,?,?,?,?)";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse("1956-09-21");
update(sql1,"王菲","女",50,"wangfei@gmail.com",new Date(date.getTime()));//占位符5个,故参数有5个*/
/*/2,删除一条记录
String sql2 = "delete from students where id = ?";
update(sql2,3);
update(sql2,4);//*/
//3,修改一条记录
String sql3 = "update students set name = ? where id = ?";
update(sql3,"王菲",5);//*/
}
//写一个通用的增删改的操作方法
public void update(String sql, Object ...args) {//sql中的占位符个数与可变参数args的长度一致
//先初始化
Connection conn = null;
PreparedStatement ps = null;
try {
//获取数据库连接
conn = JDBCUtils.getConnection();
//预编译,获取PreparedStatement的实例
ps = conn.prepareStatement(sql);
//填充占位符(用循环实现)
for (int i = 0;i < args.length;i++)
ps.setObject(i+1,args[i]);//注意参数的声明!
//执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//释放资源
JDBCUtils.closeResource(conn,ps);
}
}
}
- 工具类
package com.ljh.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/*
存放操作数据库的工具类
*/
public class JDBCUtils {
//获取数据库的连接
public static Connection getConnection() throws Exception {
//1,读取配置文件中的4个基本信息
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,加载驱动Driver
Class.forName(driverClass);
//3,获取连接
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
}
//关闭连接和Statement的操作(释放资源)
public static void closeResource(Connection conn, Statement ps) {
//7,释放资源
if(ps != null) {
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
- 测试结果:
三、对数据表的查询操作
- Employee类:
package com.ljh.bean;
import java.sql.Date;
/*
ORM编程思想:Object relation mapping
1,一个数据表对应一个java类
2,表里的一条记录对应java类的一个对象
3,表里的一个字段对应java类的一个属性
*/
public class Employee {
private long id;//java.lang里面对应的是long型,故要改成long才行
private String name;
private String email;
private Date birth;
//创建一个空方法
public Employee(){
super();
}
public Employee(int id, String name, String email, Date birth) {
super();
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
public long 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 "Employee{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", birth=" + birth +
'}';
}
}
- 针对某一个表的查询,返回一条记录
- 测试代码1.0
package com.ljh.jdbc;
import com.ljh.bean.Employee;
import com.ljh.util.JDBCUtils;
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.*;
/*
针对employee的查询操作
*/
public class EmployeeForQuery {
//测试通用方法
@Test
public void testQueryForEmployee() {
String sql = "select id,name,email,birth from employee where id = ?";
Employee employee = queryForEmployee(sql,1);
System.out.println(employee);
}
//针对employee的通用查询操作方法
public Employee queryForEmployee(String sql, Object ...args) {
/*
针对表的字段名与类的属性名不同的情况:
1,必须声明sql时使用类的属性名来命名字段的别名
2,使用ResultSetMetaData时,用getColumnLabel()替换getColumnName()来获取字段的别名
说明:使用sql中没有用到别名,则用getColumnLabel()获取就是列名
*/
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
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();
if(rs.next()){
//先创建一个当前要处理的数据对应的对象
Employee emp = new Employee();
//处理结果集第一行数据中的每一列
for (int i = 0;i < columnCount;i++){
//获取每一列的值
Object columnValue = rs.getObject(i+1);
//获取每一列的列名
/*
getColumnName:获取列的列名,
getColumnLabel:获取列的别名(推荐)
*/
//String columnName = rsmd.getColumnName(i+1);
String columnLabel = rsmd.getColumnLabel(i+1);
//给emp对象指定的columnName属性赋值为columnValue,通过反射实现
Field field = Employee.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(emp,columnValue);//将每一列的值赋值给对象emp对应的属性
}
return emp;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps,rs);
}
return null;
}
- 针对表的字段名与类的属性名不同的情况
- 必须声明sql时使用类的属性名来命名字段的别名
- 使用
ResultSetMetaData
时,用getColumnLabel()
替换getColumnName()
来获取字段的别名
- 说明:使用sql中没有用到别名,则用
getColumnLabel()
获取就是列名
- 测试代码2.0
@Test
public void testQuery() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id,name,email,birth from employee where id = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1,2);//填充占位符
//执行,并返回一个结果集
resultSet = ps.executeQuery();
//处理结果集
if(resultSet.next()){//next():判断结果集的下一条有无数据,如果有数据返回则返回true并指针下移,无则返回false
//获取当前数据的各个字段值
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
//打印数据,将数据封装成一个对象,再打印
Employee employee = new Employee(id,name,email,birth);
System.out.println(employee);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//释放资源
JDBCUtils.closeResource(conn,ps,resultSet);
}
}
}
- 针对不同表的查询,返回一条记录
package com.ljh.jdbc;
import com.ljh.bean.Employee;
import com.ljh.util.JDBCUtils;
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
/*
用PreparedStatement实现对不同表的通用查询方法
*/
public class PreparedStatementQueryTest {
//测试针对不同表的查询
@Test
public void testGetInstance(){
String sql = "select id,name,email from employee where id = ?";
Employee employee = getInstance(Employee.class,sql,2);
System.out.println(employee);
}
//通用查询方法(泛型方法)
public <T> T getInstance(Class<T> clazz, String sql, Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
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();
if(rs.next()){
//创建一个类
T t = clazz.newInstance();
for (int i = 0;i < columnCount;i++){
//获取每一列的值
Object columnValue = rs.getObject(i+1);
//获取每一列的列名(用别名)
String columnLabel = rsmd.getColumnLabel(i+1);
//与类的对象属性对应上
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps,rs);
}
return null;
}
}
- 针对不同表的查询,返回多条记录
//测试getForList
@Test
public void testGetForList() {
String sql = "select id,name,email from employee where id < ?";//注意这里是小于,不是等于,因为要查询多条记录
List<Employee> list = getForList(Employee.class,sql,4);
list.forEach(System.out::println);//打印方法第一次见。。
}
//不同表的查询,返回多条记录(使用集合,泛型)
public <T> List<T> getForList(Class<T> clazz, String sql, Object... args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
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<T> list = new ArrayList<T>();
while (rs.next()) {//原来的if变while
//创建一个类
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取每一列的值
Object columnValue = rs.getObject(i + 1);
//获取每一列的列名(用别名)
String columnLabel = rsmd.getColumnLabel(i + 1);
//与类的对象属性对应上
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
//在集合中添加元素t
list.add(t);
}
return list;//返回一个集合,如果没有查询到数据,则返回的集合长度为0
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
- 测试结果:
四、批量插入
- 方式一,用
Statement
,不推荐,会有SQL注入的风险 - 方式二,用
PreparedStatement
//方式二,用PreparedStatement
@Test
public void testInsert2() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
//插入2w个数据
for(int i = 1;i <= 20000;i++){
ps.setObject(1,"name_"+i);
ps.execute();
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps);
}
}
- 方式三,对方式二的优化
//方式三,对方式二的优化
@Test
public void testInsert3() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
//插入2w个数据
for(int i = 1;i <= 20000;i++){
ps.setObject(1,"name_"+i);
//1.“攒”sql
ps.addBatch();
//每500个数据执行一次
if(i % 500 == 0){
//2.执行batch
ps.executeBatch();
//3.清空batch
ps.clearBatch();
}
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps);
}
}
- 方式四,对方式三的优化,设置连接时不允许自动提交数据
//方式四,对方式三的优化,设置连接时不允许自动提交数据
@Test
public void testInsert4() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
//获取连接时设置不允许自动提交数据
conn.setAutoCommit(false);
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
//插入2w个数据
for(int i = 1;i <= 20000;i++){
ps.setObject(1,"name_"+i);
//1.攒sql
ps.addBatch();
if(i % 500 == 0){
//2.执行batch
ps.executeBatch();
//3.清空batch
ps.clearBatch();
}
}
//全部执行完后再提交数据
conn.commit();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps);
}
}
五、创建BaseDAO
DAO:Data Access Object,访问数据类和接口
- EmployeeDAO接口
package com.ljh.dao1;
import com.ljh.bean.Employee;
import java.sql.Connection;
import java.util.Date;
import java.util.List;
/**
* 此接口用于规范针对employee的常用操作
*/
public interface EmployeeDAO {
/**
* 将emp对象添加到数据库中
* @param conn
* @param emp
*/
void insert(Connection conn, Employee emp);
/**
* 根据对象的id删除表中一条记录
* @param conn
* @param id
*/
void deleteById(Connection conn, int id);
/**
* 根据内存中emp对象,修改数据库中的指定记录
* @param conn
* @param emp
*/
void update(Connection conn, Employee emp);
/**
* 根据id查询并得到对应对象
* @param conn
* @param id
* @return
*/
Employee getEmployee(Connection conn, int id);
/**
* 查询表中所有数据集合
* @param conn
* @return
*/
List<Employee> getAll(Connection conn);
/**
*返回数据库中数据的条目数
* @param conn
* @return
*/
Long getCount(Connection conn);
/**
* 返回表中最大的生日
* @param conn
* @return
*/
Date getMaxDate(Connection conn);
}
- BaseDAO类1.0
package com.ljh.dao1;
import com.ljh.util.JDBCUtils;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* 封装了针对数据表的通用操作
*/
public abstract class BaseDAO {//封装类
//增删改操作(考虑上事务)
public void update(Connection conn, String sql, Object ...args) {//sql中的占位符个数与可变参数args的长度一致
//先初始化
PreparedStatement ps = null;
try {
//预编译,获取PreparedStatement的实例
ps = conn.prepareStatement(sql);
//填充占位符(用循环实现)
for (int i = 0;i < args.length;i++)
ps.setObject(i+1,args[i]);//注意参数的声明!
//执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//释放资源
JDBCUtils.closeResource(null,ps);
}
}
//查询操作,返回数据表中一条记录(考虑到事务)
public <T> T getInstance(Connection conn, Class<T> clazz, 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();
if(rs.next()){
//创建一个类
T t = clazz.newInstance();
for (int i = 0;i < columnCount;i++){
//获取每一列的值
Object columnValue = rs.getObject(i+1);
//获取每一列的列名(用别名)
String columnLabel = rsmd.getColumnLabel(i+1);
//与类的对象属性对应上
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
//查询操作,返回数据表中多条记录(考虑到事务)
public <T> List<T> getForList(Connection conn, Class<T> clazz, 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<T> list = new ArrayList<T>();
while (rs.next()) {
//创建一个类
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取每一列的值
Object columnValue = rs.getObject(i + 1);
//获取每一列的列名(用别名)
String columnLabel = rsmd.getColumnLabel(i + 1);
//与类的对象属性对应上
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
//在集合中添加元素t
list.add(t);
}
return list;//返回一个集合,如果没有查询到数据,则返回的集合长度为0
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
//用于查询一些特殊值的通用方法(比如数据的条目数)
public <E> 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 throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
}
- EmployeeDao实现类1.0
package com.ljh.dao1;
import com.ljh.bean.Employee;
import java.sql.Connection;
import java.util.Date;
import java.util.List;
/**
* 实现EmployeeDAO接口并继承BaseDAO
*/
public class EmployeeDAOImpl extends BaseDAO implements EmployeeDAO {
@Override
public void insert(Connection conn, Employee emp) {
String sql = "insert into employee(id,name,email,birth)values(?,?,?,?)";
update(conn,sql,emp.getId(),emp.getName(),emp.getEmail(),emp.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {
String sql = "delete from employee where id = ?";
update(conn,sql,id);
}
@Override
public void update(Connection conn, Employee emp) {
String sql = "update employee set name = ?,email = ?,birth = ? where id = ?";
update(conn,sql,emp.getName(),emp.getEmail(),emp.getBirth(),emp.getId());
}
@Override
public Employee getEmployee(Connection conn, int id) {
String sql = "select id,name,email,birth from employee where id = ?";
return getInstance(conn,Employee.class,sql,id);
}
@Override
public List<Employee> getAll(Connection conn) {
String sql = "select id,name,email,birth from employee";
return getForList(conn,Employee.class,sql);
}
@Override
public Long getCount(Connection conn) {
String sql = "select count(*) from employee";
return getValue(conn,sql);
}
@Override
public Date getMaxDate(Connection conn) {
String sql = "select max(birth) from employee";
return getValue(conn,sql);
}
}
- BaseDao2.0
PS:与BaseDao1.0的区别在于多了获取当前BaseDAO的子类继承的父类的泛型的操作,实现接口时可以不用再传入Employee对象
package com.ljh.dao2;
import com.ljh.util.JDBCUtils;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* 封装了针对数据表的通用操作
*/
public abstract class BaseDAO<T> {//封装类
private Class<T> clazz = null;
{
//获取当前BaseDAO的子类继承的父类的泛型
Type genericSuperclass = this.getClass().getGenericSuperclass();
ParameterizedType paramType = (ParameterizedType)genericSuperclass;
Type[] typeArguments = paramType.getActualTypeArguments();//获取父类的泛型
clazz = (Class<T>) typeArguments[0];//泛型的第一个参数,就是T
}
//增删改操作(考虑上事务)
public void update(Connection conn, String sql, Object ...args) {//sql中的占位符个数与可变参数args的长度一致
//先初始化
PreparedStatement ps = null;
try {
//预编译,获取PreparedStatement的实例
ps = conn.prepareStatement(sql);
//填充占位符(用循环实现)
for (int i = 0;i < args.length;i++)
ps.setObject(i+1,args[i]);//注意参数的声明!
//执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//释放资源
JDBCUtils.closeResource(null,ps);
}
}
//查询操作,返回数据表中一条记录(考虑到事务)
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 rsmd = rs.getMetaData();
//获取列数
int columnCount = rsmd.getColumnCount();
if(rs.next()){
//创建一个类
T t = clazz.newInstance();
for (int i = 0;i < columnCount;i++){
//获取每一列的值
Object columnValue = rs.getObject(i+1);
//获取每一列的列名(用别名)
String columnLabel = rsmd.getColumnLabel(i+1);
//与类的对象属性对应上
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
//查询操作,返回数据表中多条记录(考虑到事务)
public List<T> 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<T> list = new ArrayList<T>();
while (rs.next()) {
//创建一个类
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取每一列的值
Object columnValue = rs.getObject(i + 1);
//获取每一列的列名(用别名)
String columnLabel = rsmd.getColumnLabel(i + 1);
//与类的对象属性对应上
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
//在集合中添加元素t
list.add(t);
}
return list;//返回一个集合,如果没有查询到数据,则返回的集合长度为0
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
//用于查询一些特殊值的通用方法(比如数据的条目数)
public <E> 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 throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
}
- Employee实现类2.0
PS:与1的区别是
getEmployee
和getAll
方法实现中减少了Employee.class
,不用再传入对象,因为只是针对Employee一个类,只需获取父类的泛型(Employee)即可。
package com.ljh.dao2;
import com.ljh.bean.Employee;
import java.sql.Connection;
import java.util.Date;
import java.util.List;
/**
* 实现EmployeeDAO接口并继承BaseDAO
*/
public class EmployeeDAOImpl extends BaseDAO<Employee> implements EmployeeDAO {
@Override
public void insert(Connection conn, Employee emp) {
String sql = "insert into employee(id,name,email,birth)values(?,?,?,?)";
update(conn,sql,emp.getId(),emp.getName(),emp.getEmail(),emp.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {
String sql = "delete from employee where id = ?";
update(conn,sql,id);
}
@Override
public void update(Connection conn, Employee emp) {
String sql = "update employee set name = ?,email = ?,birth = ? where id = ?";
update(conn,sql,emp.getName(),emp.getEmail(),emp.getBirth(),emp.getId());
}
@Override
public Employee getEmployee(Connection conn, int id) {
String sql = "select id,name,email,birth from employee where id = ?";
return getInstance(conn,sql,id);
}
@Override
public List<Employee> getAll(Connection conn) {
String sql = "select id,name,email,birth from employee";
return getForList(conn,sql);
}
@Override
public Long getCount(Connection conn) {
String sql = "select count(*) from employee";
return getValue(conn,sql);
}
@Override
public Date getMaxDate(Connection conn) {
String sql = "select max(birth) from employee";
return getValue(conn,sql);
}
}
六、使用Druid数据库连接池
package com.ljh.connection;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
/**
* 测试Druid数据连接池
*/
public class DruidTest {
//用配置文件的方法
@Test
public void getConnection() throws Exception {
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
//关键代码
DataSource source = DruidDataSourceFactory.createDataSource(pros);
Connection conn = source.getConnection();
System.out.println(conn);
}
}
- 配置文件(druid.properties)
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=Myroot_ljh
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=10
七、使用DBUtils实现CRUD操作
package com.ljh.dbutils;
import com.ljh.bean.Employee;
import com.ljh.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* commons-dbutils是Apache组织提供的一个开源JDBC工具类库,封装了针对于数据库的增删改操作
*/
public class QueryRunnerTest {
/**
* 测试插入
*/
@Test
public void testInsert() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection1();
String sql = "insert into employee(name,email,birth)values(?,?,?)";
int insertCount = runner.update(conn,sql,"谢霆锋","xietingfeng@126.com","1890-02-12");
System.out.println("添加了"+insertCount+"条记录");
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
/**
* 测试查询,返回一条记录
* BeanHandler:ResultSetHandler接口的实现类,用于封装表中的一条记录
*/
@Test
public void testQuery1() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection1();
String sql = "select id,name,email,birth from employee where id = ?";
BeanHandler<Employee> handler = new BeanHandler<>(Employee.class);
Employee emp = runner.query(conn,sql,handler,6);
System.out.println(emp);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
/**
* 测试查询,返回多条记录
* BeanListHandler:ResultSetHandler接口的实现类,用于封装表中的多条记录构成的集合
*/
@Test
public void testQuery2() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection1();
String sql = "select id,name,email,birth from employee where id < ?";
BeanListHandler<Employee> handler = new BeanListHandler<>(Employee.class);
List<Employee> list = runner.query(conn,sql,handler,6);
//list的打印
list.forEach(System.out::println);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
/**
* 测试查询,返回一条记录
* MapHandler:ResultSetHandler接口的实现类,用于封装表中的一条记录
* 将字段及相应字段的值作为map中key和value(键值对)
*/
@Test
public void testQuery3() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection1();
String sql = "select id,name,email,birth from employee where id = ?";
MapHandler handler = new MapHandler();
Map<String,Object> map = runner.query(conn,sql,handler,6);
System.out.println(map);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
/**
* 测试查询,返回多条记录
* MapListHandler:ResultSetHandler接口的实现类,用于封装表中的多条记录的集合
* 将字段及相应字段的值作为map中key和value(键值对),将这些map添加到list中
*/
@Test
public void testQuery4() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection1();
String sql = "select id,name,email,birth from employee where id < ?";
MapListHandler handler = new MapListHandler();
List<Map<String,Object>> list = runner.query(conn,sql,handler,6);
//list的打印
list.forEach(System.out::println);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
/**
* 返回其他值
*/
@Test
public void testQuery5() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection1();
String sql = "select count(*) from employee";
ScalarHandler handler = new ScalarHandler();
Long count = (Long) runner.query(conn,sql,handler);
System.out.println(count);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
}