解决重复问题
考虑JDBC操作都遵循6步骤
1. 加载驱动
只要加载一次就行了
优先被加载
static代码块
2. 获取连接
封装成一个获取连接的方法
3. 获取SQL执行器
4. 执行SQL
5. 处理结果
6. 关闭资源
封装关闭资源的方法
封装一个工具类
package march.part0320.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JdbcUtil {
static {
//1. 加载驱动
//只要JdbcUtil加载到内存中,static代码块就会被优先加载,且只会被加载一次
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection () {
Connection connection = null;
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost/fairykunkun","root","root");
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//调用方法的时候
public static void close (AutoCloseable ... closeables) {
//关闭资源需要遵从,后使用先关闭
for (int i = 0 ; i < closeables.length ; i++) {
try {
closeables[i].close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
UserDaoImpl中使用
package march.part0320.dao.impl;
import march.part0320.dao.UserDao;
import march.part0320.entity.User;
import march.part0320.util.DateUtil;
import march.part0320.util.JdbcUtil;
import java.sql.*;
public class UserDaoImpl implements UserDao {
@Override
public void add(User user) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
connection = JdbcUtil.getConnection();
PreparedStatement preparedStatement = null;
try {
String sql = "insert into t_user(t_username,t_pwd,t_createtime) values (?,?,now())";
//预编译,其实就是mysql在编译该语句,最终sql语句只会在数据缓冲区存储一份
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,user.getUserName());//占位符从左往右依次为1,2,3
preparedStatement.setString(2,user.getPwd());
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(preparedStatement,connection);
}
}
@Override
public User query(String userName, String pwd) {
User user = null;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
connection = JdbcUtil.getConnection();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
String sql = "select t_username,t_pwd,t_createtime from t_user where t_username = ? and t_pwd = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,userName);
preparedStatement.setString(2,pwd);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
user = new User(resultSet.getString("t_username"),resultSet.getString("t_pwd"), DateUtil.strToDate(resultSet.getString("t_createtime"),"yyyy-MM-dd HH:mm:ss"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(resultSet,preparedStatement,connection);
}
return user;
}
}
引入数据库配置文件
数据库的版本不一样,可能驱动不一样
数据库名、ip、用户名、密码等都有可能会变
希望封装的JdbcUtil更加灵活点,引入数据库的相关配置文件
配置文件准备采用什么格式?properties
配置文件需要配置哪些信息?变化的数据。
在src下创建配置文件,统一为db.properties
配置信息(由我们自己设计)
db.driver = com.mysql.jdbc.Driver
db.url = jdbc:mysql://localhost/fairykunkun?characterEncoding=utf8
db.username = root
db.password = root
应用配置文件
package march.part0320.util;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class JdbcUtil {
private static Properties properties = new Properties();
static {
try {
properties.load(JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
try {
Class.forName(properties.getProperty("db.driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection () {
Connection connection = null;
try {
connection = DriverManager.getConnection(properties.getProperty("db.url"),properties.getProperty("db.username"),properties.getProperty("db.password"));
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//调用方法的时候
public static void close (AutoCloseable ... closeables) {
//关闭资源需要遵从,后使用先关闭
for (int i = 0 ; i < closeables.length ; i++) {
if (null != closeables[i]) {
try {
closeables[i].close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
引入模板类
设计DeptDao
package march.part0320.dao.impl;
import march.part0320.dao.DeptDao;
import march.part0320.entity.Dept;
import march.part0320.util.JdbcUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DeptDaoImpl implements DeptDao {
@Override
public void add(Dept dept) {
Connection connection = null;
connection = JdbcUtil.getConnection();
PreparedStatement preparedStatement = null;
String sql = "insert into dept(deptno,dname,loc) values (?,?,?)";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,dept.getDeptno());
preparedStatement.setString(2,dept.getDname());
preparedStatement.setString(3,dept.getLoc());
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(preparedStatement,connection);
}
}
}
测试
package march.part0320.test;
import march.part0320.dao.DeptDao;
import march.part0320.dao.impl.DeptDaoImpl;
import march.part0320.entity.Dept;
public class TestDeptDao {
public static void main(String[] args) {
DeptDao deptDao = new DeptDaoImpl();
deptDao.add(new Dept(80,"chating","L104"));
}
}
造成重复的根本原因是什么?JDBC操作都是相同的步骤,只是SQL语句+参数不一样罢了。
现在生活中的模板 --->抄一个模板--->改一点点东西。
整一个模板 --->专门处理insert、update、delete
package march.part0320.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JdbcTemplate {
/**
* 专门处理insert update delete的模板方法
* @param sql
* @param preparedStatements 参数列表 个数和sql中的占位符一致
*/
public static void executeUpdate(String sql,Object ... preparedStatements) {
Connection connection = null;
connection = JdbcUtil.getConnection();
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
setPreparedStatements(preparedStatement,preparedStatements);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(preparedStatement,connection);
}
}
private static void setPreparedStatements(PreparedStatement preparedStatement,Object ... preparedStatements) {
if (!(null == preparedStatement || preparedStatements.length == 0)) {//有参数
for (int i = 0 ; i < preparedStatements.length ; i++) {
try {
preparedStatement.setObject((i + 1),preparedStatements[i]);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
try {
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package march.part0320.dao.impl;
import march.part0320.dao.DeptDao;
import march.part0320.entity.Dept;
import march.part0320.util.JdbcTemplate;
public class DeptDaoImpl implements DeptDao {
@Override
public void add(Dept dept) {
String sql = "insert into dept(deptno,dname,loc) values (?,?,?)";
JdbcTemplate.executeUpdate(sql,dept.getDeptno(),dept.getDname(),dept.getLoc());
}
}
package march.part0320.dao.impl;
import march.part0320.dao.UserDao;
import march.part0320.entity.User;
import march.part0320.util.DateUtil;
import march.part0320.util.JdbcTemplate;
import march.part0320.util.JdbcUtil;
import java.sql.*;
public class UserDaoImpl implements UserDao {
@Override
public void add(User user) {
String sql = "insert into t_user(t_username,t_pwd,t_createtime) values (?,?,now())";
JdbcTemplate.executeUpdate(sql,user.getUserName(),user.getPwd());
}
@Override
public User query(String userName, String pwd) {
User user = null;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
connection = JdbcUtil.getConnection();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
String sql = "select t_username,t_pwd,t_createtime from t_user where t_username = ? and t_pwd = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,userName);
preparedStatement.setString(2,pwd);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
user = new User(resultSet.getString("t_username"),resultSet.getString("t_pwd"), DateUtil.strToDate(resultSet.getString("t_createtime"),"yyyy-MM-dd HH:mm:ss"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(resultSet,preparedStatement,connection);
}
return user;
}
}