在没学习框架前的关于JDBC的一些笔记
JDBC程序编写步骤
1、获取数据库连接
方式一
Driver driver = new com.mysql.jdbc.Driver();
//jdbc:mysql协议
//localhost:ip地址
//3306:mysql默认端口号
//test:数据库名称
String url = "jdbc:mysql://localhost:3306/test";
Properties info = new Properties(); //用户名和密码的封装类
info.setProperty("user","root"); //设置用户名
info.setProperty("password","root");//设置密码
Connection conn = driver.connect(url, info); //创建连接
System.out.println(conn); //打印
方式二
对方式一的迭代:方式一存在的“new com.mysql.jdbc.Driver()”是第三方API,不好做代码移植
用反射获取Driver实现类对象
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
String url = "jdbc:mysql://localhost:3306/test";
Properties info = new Properties();
info.setProperty("user","root");
info.setProperty("password","root");
Connection conn = driver.connect(url, info);
System.out.println(conn);
方式三
使用DriverManager替换Driver类
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "root";
//注册驱动
DriverManager.registerDriver(driver);
//获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
方式四
对驱动进行优化,只是加载驱动,不用显示注册驱动
在MySQL的Driver实现类中,静态类实现了注册
//1.数据库连接的基本信息:
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "root";
//2.获取Driver实现类的对象
Class.forName("com.mysql.jdbc.Driver"); //mysql自动注册驱动
//Driver driver = (Driver) clazz.newInstance();
//DriverManager.registerDriver(driver);
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
方式五(★)
将基本信息写到配置文件中,用读取配置文件的方式获取连接
好处: |
---|
只用通过改配置文件就能更换数据库,实现了代码与数据的分离,实现了解耦 |
如果需要修改配置文件信息,就可以避免程序重新打包 |
新建jdbc.properties文件(”=“前后不要加空格)
url=jdbc:mysql://localhost:3306/test
user=root
password=root
driverClass=com.mysql.jdbc.Driver
//读取配置文件
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
//加载驱动
Class.forName(driverClass);
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
2、创建Statement对象
封装数据库连接和关闭操作
数据库连接
public static Connection getConnection() throws Exception {
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
Class.forName(driverClass);
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
关闭连接
public static void closeResource(Connection conn, Statement preparedStatement){
try {
if (preparedStatement != null)
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
PreparedStatement的CRUD操作
1.添加操作
@Test
public void testInsert(){
Connection conn = null;
PreparedStatement preparedStatement = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into customers(name, email, birth) values(?,?,?)";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1,"张三");
preparedStatement.setString(2,"123@123.com");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse("2021-6-11");
preparedStatement.setDate(3,new Date(date.getTime()));
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,preparedStatement);
}
}
2.修改操作
@Test
public void testUpdate() {
Connection conn = null;
PreparedStatement preparedStatement = null;
try {
conn = JDBCUtils.getConnection();
String sql = "update customers set name = ? where id = ?";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, "莫扎特");
preparedStatement.setInt(2, 18);
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, preparedStatement);
}
}
3.删除操作
@Test
public void testDelete(){
Connection conn = null;
PreparedStatement preparedStatement = null;
try {
conn = JDBCUtils.getConnection();
String sql = "delete from customers where id = ?";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1, 19);
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, preparedStatement);
}
}
通用的增删改操作
@Test
public void update(String sql, Object ...args){ //sql中的占位符的个数与可变形参的长度相同
Connection conn = null;
PreparedStatement preparedStatement = null;
try {
conn = JDBCUtils.getConnection();
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,preparedStatement);
}
}
调用方法测试
@Test
public void testCommonUpdate(){
String sql = "delete from customers where id = ?";
update(sql,20);
sql = "insert into customers(name, email, birth) values(?,?,?)";
update(sql,"张三","123@123.com",null);
}
4.查询操作
举例:对Customers表的一条数据进行查询
@Test
public void testQuery1(){
Connection conn = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from customers where id = ?";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1,1);
//返回结果集
resultSet = preparedStatement.executeQuery();
//处理结果集
if (resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
Customer customer = new Customer(id, name, email, birth);
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, preparedStatement, resultSet);
}
}
针对customers表的一条通用查询操作
public Customer queryForCustomer(String sql, Object ...args) {
Connection conn = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
resultSet = preparedStatement.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
if (resultSet.next()){
Customer customer = new Customer();
for (int i = 0; i < columnCount; i++) {
Object columnValue = resultSet.getObject(i+1);
//获取每列的列名
String ColumnLabel = rsmd.getColumnLabel(i+1);
//通过反射的方式对对象进行赋值
Field field = Customer.class.getDeclaredField(ColumnLabel);
field.setAccessible(true);
field.set(customer,columnValue);
}
return customer;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,preparedStatement,resultSet);
}
return null;
}
调用方法测试
@Test
public void testQueryForCustomer(){
String sql = "select name,id,email from customers where id = ?";
Customer customer = queryForCustomer(sql,2);
System.out.println(customer);
}
查询操作的流程
针对不同表的多条通用查询操作
public <T> List<T> getForList(Class<T> clazz, String sql, Object ...args) {
Connection conn = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
resultSet = preparedStatement.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
ArrayList<T> arrayList = new ArrayList<>();
while (resultSet.next()){ //while循环多条记录
T t = clazz.newInstance(); //创建类泛型对象
for (int i = 0; i < columnCount; i++) {
Object columnValue = resultSet.getObject(i+1);
//获取每列的列名
String columnLabel = rsmd.getColumnLabel(i+1);
//通过反射的方式对对象进行复制
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
arrayList.add(t); //多条记录添加list
}
return arrayList;
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,preparedStatement,resultSet);
}
return null;
}
测试方法调用
@Test
public void testGetForList(){
String sql = "select id, name, email from customers where id <= ?";
List<Customer> list = getForList(Customer.class, sql, 3);
list.forEach(System.out::println);
System.out.println("-----------------------------");
String sql1 = "select order_id orderId, order_name orderName, order_date orderData from `order` where order_id < ?";
List<Order> list1 = getForList(Order.class, sql1, 3);
list1.forEach(System.out::println);
}
PreparedStatement的批量插入操作
1.在配置文件jdbc.properties中添加,允许batch操作
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
2.使用5.1.37及以上的jar包支持批操作
3.编写batch程序
@Test
public void testInsert(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
//设置不允许自动提交
connection.setAutoCommit(false);
String sql = "insert into goods(name) values (?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i <= 20000; i++) {
preparedStatement.setObject(1, "name_" + i);
//使用batch攒数据提交
preparedStatement.addBatch();
if (i % 500 == 0 ){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
//统一提交
connection.commit();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}
事务处理
1.关闭连接时会自动提交数据,取消连接的每次关闭,更改Update方法
public void update(Connection conn, String sql, Object ...args){ //传入同一个连接
PreparedStatement preparedStatement = null;
try {
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,preparedStatement); //不关闭连接
}
}
2.每条语句都会自动提交,关闭自动提交(AutoCommit)功能
@Test
public void testUpdateWithTx(){
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
conn.setAutoCommit(false); //设置取消数据自动提交
String sql1 = "update user_table set balance = balance - 100 where user = ?";
update(conn, sql1, "AA");
//模拟网络异常
System.out.println(10 / 0);
String sql2 = "update user_table set balance = balance + 100 where user = ?";
update(conn, sql2, "BB");
conn.commit(); //提交数据
} catch (Exception e) {
e.printStackTrace();
try {
conn.rollback(); //回滚
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}finally {
try {
conn.setAutoCommit(true);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
JDBCUtils.closeResource(conn, null);
}
}
DAO持久层
1.将针对于数据库的通用操作封装在DAO类中
//封装了针对于数据表的通用操作
public class DAO {
public void update(Connection conn, String sql, Object ...args){
PreparedStatement preparedStatement = null;
try {
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,preparedStatement);
}
}
public <T> List<T> getForList(Connection conn, Class<T> clazz, String sql, Object ...args) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
resultSet = preparedStatement.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
ArrayList<T> arrayList = new ArrayList<>();
while (resultSet.next()){
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columnValue = resultSet.getObject(i+1);
//获取每列的列名
String columnLabel = rsmd.getColumnLabel(i+1);
//通过反射的方式对对象进行复制
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
arrayList.add(t);
}
return arrayList;
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,preparedStatement,resultSet);
}
return null;
}
public <T> T queryForCustomer(Connection conn, Class<T> clazz, String sql, Object ...args) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
resultSet = preparedStatement.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
if (resultSet.next()){
T t = clazz.newInstance();
Customer customer = new Customer();
for (int i = 0; i < columnCount; i++) {
Object columnValue = resultSet.getObject(i+1);
//获取每列的列名
String columnLabel = rsmd.getColumnLabel(i+1);
//通过反射的方式对对象进行复制
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(customer,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,preparedStatement,resultSet);
}
return null;
}
}
2.创建一个针对于某数据的DAO接口继承DAO类
//此接口用于规范针对于customers表的常用操作
public interface CustomerDAO {
void insert(Connection conn, Customer customer);
void deleteById(Connection conn, int id);
void updateNameById(Connection conn, int id, Customer customer);
void getCustomerById(Connection conn, int id);
List<Customer> getAll(Connection conn);
}
3.提供接口实现类
public class CustomerDAOImpl extends DAO implements CustomerDAO {
@Override
public void insert(Connection conn, Customer customer) {
String sql = "insert into customers(name, email, birth) values(?,?,?)";
update(conn, sql, customer.getName(), customer.getEmail(), customer.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {
String sql = "delete from customers where id = ?";
update(conn,sql,id);
}
@Override
public void updateNameById(Connection conn, int id, Customer customer) {
String sql = "update customers set name = ? where id = ?";
update(conn, sql, customer.getName(), id);
}
@Override
public Customer getCustomerById(Connection conn, int id) {
String sql = "select id, name, email, birth from customers where id = ?";
Customer customer = queryForCustomer(conn, Customer.class, sql, id);
return customer;
}
@Override
public List<Customer> getAll(Connection conn) {
String sql = "select id, name. email, birth from customers";
List<Customer> list = getForList(conn, Customer.class, sql);
return list;
}
}
4.编写测试类
public class CustomerDAOImplTest {
private CustomerDAOImpl dao = new CustomerDAOImpl();
@Test
public void insert() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Customer customer = new Customer(1, "张三", "zhangsan@3306.com", new Date(1561516156165L));
dao.insert(conn, customer);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void deleteById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
dao.deleteById(conn, 21);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void updateNameById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Customer customer= new Customer();
customer.setName("李四");
customer.setEmail("lisi@3306.com");
dao.updateNameById(conn, 22, customer );
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void getCustomerById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id, name, email, birth from customers where id = ?";
Customer customer = dao.queryForCustomer(conn, Customer.class, sql, 13);
System.out.println(customer);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void getAll() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id, name, email, birth from customers";
List<Customer> list = dao.getForList(conn, Customer.class, sql);
list.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
}
JDBC数据库连接池
建立数据库连接池的必要性 |
---|
使用DriverManager,每次都要向数据库建立连接都要创建一个connection对象,会大大的消耗资源,且没得到很好的利用 |
每次连接使用完都得断开,不断开连接会导致内存泄漏 |
这种开发不能控制被创建的连接对象数 |
以Druid连接池为例
1.创建druid.properties文件
url=jdbc:mysql://localhost:3306/test
username=root
password=root
driverClassName=com.mysql.jdbc.Driver
2.创建连接
private static DataSource source;
static { //静态代码块只执行一次,生成一个连接池,避免每次创建连接都生成连接池
try {
Properties properties = new Properties();
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
source = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
Connection connection = source.getConnection();
return connection;
}
dbutils
commons-dbutils 是Apache 组织提供的一个开源JDBC 工具类库,封装了针对于数据库的增删改查操作
1.导包【commons-dbutils-1.3.jar】
2.测试CRUD
@Test
public void testInsert(){
Connection conn = null;
try {
QueryRunner queryRunner = new QueryRunner();
conn = DruidTest.getConnection();
String sql = "insert into customers(name, email, birth) values(?,?,?)";
queryRunner.update(conn, sql, "张三", "zhangsan@3306.com", "1998-01-02");
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testDelete(){
Connection conn = null;
try {
QueryRunner queryRunner = new QueryRunner();
conn = DruidTest.getConnection();
String sql = "delete from customers where id = ?";
queryRunner.update(conn, sql, "23");
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testUpdate(){
Connection conn = null;
try {
QueryRunner queryRunner = new QueryRunner();
conn = DruidTest.getConnection();
String sql = "update customers set name = ? where id = ?";
queryRunner.update(conn, sql, "李四","23");
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testQueryForBean(){
//BeanHandler用于封装表中的一条记录
Connection conn = null;
try {
QueryRunner queryRunner = new QueryRunner();
conn = DruidTest.getConnection();
String sql = "select id, name, email, birth from customers where id = ?";
BeanHandler<Customer> beanHandler = new BeanHandler<>(Customer.class);
Customer customer = queryRunner.query(conn, sql, beanHandler, 4);
System.out.println(customer);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testQueryForList(){
//BeanListHandler用于封装表中的多条记录
Connection conn = null;
try {
QueryRunner queryRunner = new QueryRunner();
conn = DruidTest.getConnection();
String sql = "select id, name, email, birth from customers where id <= ?";
BeanListHandler<Customer> beanListHandler = new BeanListHandler<>(Customer.class);
List<Customer> list = queryRunner.query(conn, sql, beanListHandler, 4);
list.forEach(System.out::println);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testQueryForSpecial(){
//ScalarHandler查询sql函数相关
Connection conn = null;
try {
QueryRunner queryRunner = new QueryRunner();
conn = DruidTest.getConnection();
String sql = "select count(*) from customers";
String sql2 = "select max(birth) from customers";
ScalarHandler scalarHandler = new ScalarHandler();
Object o = queryRunner.query(conn, sql, scalarHandler);
Object o2 = queryRunner.query(conn, sql2, scalarHandler);
System.out.println(o);
System.out.println(o2);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
3.关闭资源
public static void closeResource(Connection conn, Statement preparedStatement, ResultSet resultSet){
DbUtils.closeQuietly(conn);
DbUtils.closeQuietly(preparedStatement);
DbUtils.closeQuietly(resultSet);
}
Tips1: ORM编程思想
ORM编程思想(object、relational、mapping) |
---|
一个数据表对应一个Java类 |
表中的一条记录对应Java类的一个对象 |
表中的一个字段对应Java类的一个属性 |
Tips2: PreparedStatement的好处
PreparedStatement替代Statement的好处 |
---|
解决了SQL注入问题 |
代码的可读性和可维护性高 |
可以操作Blob的数据 |
实现更高效的批量操作 |