工具类封装
在前面的JDBC案例中我们不难发现,每次操纵数据库都需要进行数据库的注册、连接等大量重复的操作,是的代码变得十分的臃肿,所以此时我们应该考虑如何把重复代码提取出来,随时需要随时拿来用。
这就是工具类的封装。
案例实现:实现emp表的查询、添加、删除、修改
封装DbUtils
由于多个地方都需要使用数据库连接和释放,所以把功能封装到工具类中DbUtils
四个功能:
- 注册驱动
- 获取连接
- 释放资源
- 执行命令
db.properties信息文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/emp
user=root
password=root
自定义DbUtils工具类
public class DBUtils {
private static String driver;
private static String url;
private static String user;
private static String password;
static {
// 使用信息文件进行赋值
// 使用类加载器加载信息文件
InputStream is = DBUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties pro = new Properties();
try {
// 读取文件信息并赋值
pro.load(is);
driver = pro.getProperty("driver");
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
try {
// 注册驱动
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取数据库连接
public static Connection getConnection() {
Connection conn = null;
try {
// 获取数据库连接
conn = DriverManager.getConnection(url, user, password);
return conn;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
// 更新数据库
public static int update(String sql, Object... params) {// 接收SQL语句和数据完成数据库的操作
Connection conn = getConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
int len = ps.executeUpdate();
return len;
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(conn, ps, null);
}
return 0;
}
// 查询所有
public static List<Employee> getAll() {
List<Employee> list = new ArrayList<Employee>();
String sql = "SELECT * FROM emp;";
Connection conn = getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
int empno = rs.getInt(1);
String ename = rs.getString(2);
String job = rs.getString(3);
int mgr = rs.getInt(4);
Date hiredate = rs.getDate(5);
double sal = rs.getDouble(6);
double comm = rs.getDouble(7);
int deptno = rs.getInt(8);
Employee emp = new Employee(empno, ename, job, mgr, hiredate, sal, comm, deptno);
list.add(emp);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
// 根据ID查询
public static Employee getById(int empno) {
String sql = "SELECT * FROM emp WHERE empno=?";
Connection conn = getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
Employee emp = null;
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, empno);
rs = ps.executeQuery();
if (rs.next()) {
String ename = rs.getString(2);
String job = rs.getString(3);
int mgr = rs.getInt(4);
Date hiredate = rs.getDate(5);
double sal = rs.getDouble(6);
double comm = rs.getDouble(7);
int deptno = rs.getInt(8);
emp = new Employee(empno, ename, job, mgr, hiredate, sal, comm, deptno);
}
return emp;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
// 关闭数据库连接
public static void closeAll(Connection conn, PreparedStatement ps, ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}