前言
数据库操作的封装实际是对实体对象进行封装，封装后只需传入实体对象或实体对象类等即可对其进行简单操作。
目的是：减少代码重复，提高代码重用。
之后新建几个包分别存储不同类型的类。
项目结构如下
数据库操作封装类C3P0Utils.java
package util;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0DBUtils {
private static DataSource dataSource = null;
private static Connection con = null;
private static PreparedStatement ps = null;
private static CallableStatement callableStatement = null;
private static ResultSet rs = null;
static {
// dataSource资源只能初始化一次
dataSource = new ComboPooledDataSource("mvcApp");
}
/**
* 获取连接
*
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static DataSource getDataSource() {
return dataSource;
}
/**
* 释放连接
*
* @param connection
*/
public static void releaseConnection(Connection connection) {
try {
if (connection != null) {
connection.close();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
connection = null;
}
}
public static void close() {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("result关闭失败");
}
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("statement关闭失败");
} finally {
ps = null;
}
if (callableStatement != null) {
try {
callableStatement.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
callableStatement = null;
}
}
try {
if (con != null) {
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("Connect关闭失败");
} finally {
con = null;
}
}
// 实现增删改
public static int update(String sql, Object[] objects) throws SQLException {
int num = 0;
try {
con=getConnection();
ps = con.prepareStatement(sql);
if (objects != null && objects.length > 0) {
for (int i = 0; i < objects.length; i++) {
ps.setObject((i + 1), objects[i]);
}
}
num = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
C3P0DBUtils.close();
}
return num;
}
// 查询
public static ResultSet getResultSet(String sql, Object[] objects) {
ps = null;
try {
con=getConnection();
ps = con.prepareStatement(sql);
if (objects != null && objects.length > 0) {
for (int i = 0; i < objects.length; i++) {
ps.setObject((i + 1), objects[i]);
}
}
rs = ps.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
/**
* SQL 查询将查询结果：第一行第一列
*
* @param sql
* SQL语句
* @param params
* 参数数组，若没有参数则为null
* @return 结果集
*/
public static Object executeQuerySingle(String sql, Object[] params) {
Object object = null;
try {
con=getConnection();
// 调用SQL
ps = con.prepareStatement(sql);
// 参数赋值
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
// 执行
rs = ps.executeQuery();
if (rs.next()) {
object = rs.getObject(1);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
close();
}
return object;
}
/**
* 获取结果集，并将结果放在List中
*
* @param sql
* SQL语句 params 参数，没有则为null
* @return List 结果集
*
*/
public static List<Object> excuteQuery(String sql, Object[] params) {
// 执行SQL获得结果集
ResultSet rs = getResultSet(sql, params);
// 创建ResultSetMetaData对象
ResultSetMetaData rsmd = null;
// 结果集列数
int columnCount = 0;
try {
rsmd = rs.getMetaData();
// 获得结果集列数
columnCount = rsmd.getColumnCount();
} catch (SQLException e1) {
System.out.println(e1.getMessage());
}
// 创建List
List<Object> list = new ArrayList<Object>();
try {
// 将ResultSet的结果保存到List中
while (rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
map.put(rsmd.getColumnLabel(i), rs.getObject(i));
}
list.add(map);
// 每一个map代表一条记录，把所有记录存在list中
}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
// 关闭所有资源
close();
}
return list;
}
/**
* 存储过程带有一个输出参数的方法
*
* @param sql
* 存储过程语句
* @param params
* 参数数组
* @param outParamPos
* 输出参数位置
* @param SqlType
* 输出参数类型
* @return 输出参数的值
* @throws SQLException
*/
public static Object excuteQuery(String sql, Object[] params, int outParamPos, int SqlType) throws SQLException {
Object object = null;
con = getConnection();
try {
// 调用存储过程
// prepareCall:创建一个 CallableStatement 对象来调用数据库存储过程。
callableStatement = con.prepareCall(sql);
// 给参数赋值
if (params != null) {
for (int i = 0; i < params.length; i++) {
callableStatement.setObject(i + 1, params[i]);
}
}
// 注册输出参数
callableStatement.registerOutParameter(outParamPos, SqlType);
// 执行
callableStatement.execute();
// 得到输出参数
object = callableStatement.getObject(outParamPos);
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
// 释放资源
close();
}
return object;
}
}
对封装类进行测试
数据库有一张myguests表，对其进行增删改查
Guests.java实体类
package entity;
import java.sql.Timestamp;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @Data 该注解能够自动创建出来get、set、toString、eaquls、hashCode等方法
* @NoArgsConstructor 这个是无参构造器
* @AllArgsConstructor 全参构造器
* @Setter set方法
* @Getter get方法
*
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Guests {
private Integer id;
private String firstname;
private String lastname;
private String email;
private Timestamp reg_date;
}
其中三个注解是lombak的应用，自动创建构造方法和set，get方法等
dao持久层接口
package dao;
import java.util.List;
import entity.Guests;
public interface GuestsDao {
//增加
int insertGuests(Guests guest);
//修改
int updateGuestsName(Guests guest);
//删除
int deleteGuests(int did);
//全查
List<Guests> selectAllGuests();
List<Guests> queryAllGuests();
}
dao持久层实现
package daoImpl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import dao.GuestsDao;
import entity.Guests;
import util.C3P0DBUtils;
import util.DateUtils;
public class GuestsDaoImpl implements GuestsDao{
@Override
public int insertGuests(Guests guest) {
String sql="insert into myguests values(null,?,?,?,?)";
Object[] objects ={guest.getFirstname(),guest.getLastname(),guest.getEmail(),guest.getReg_date()};
int num = 0;
try {
num = C3P0DBUtils.update(sql, objects);
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
@Override
public int updateGuestsName(Guests guest) {
String sql="update myguests set lastname=? where id=?";
Object[] objects={guest.getLastname(),guest.getId()};
int num = 0;
try {
num = C3P0DBUtils.update(sql, objects);
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
@Override
public int deleteGuests(int id) {
String sql="delete from myguests where id=?";
Object[] objects={id};
int num = 0;
try {
num = C3P0DBUtils.update(sql, objects);
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
@Override
public List<Guests> selectAllGuests() {
String sql="select * from myguests";
List<Guests> list=null;
try {
ResultSet rs=C3P0DBUtils.getResultSet(sql, null);
list =new ArrayList<Guests>();
while (rs.next()) {
Guests guest =new Guests();
int id=rs.getInt("id");
String firstname=rs.getString("firstname");
String lastname=rs.getString("lastname");
String email=rs.getString("email");
Timestamp time=rs.getTimestamp("reg_date");
guest.setId(id);
guest.setFirstname(firstname);
guest.setLastname(lastname);
guest.setEmail(email);
guest.setReg_date(time);
list.add(guest);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Override
public List<Guests> queryAllGuests() {
String sql="select * from myguests";
List<Object> lobj=C3P0DBUtils.excuteQuery(sql,null);
List<Guests> list=new ArrayList<Guests>();
for (int i = 0; i < lobj.size(); i++) {
Guests guest = new Guests();
@SuppressWarnings("unchecked")
Map<String, Object> map=(Map<String, Object>) lobj.get(i);
guest.setEmail(map.get("email").toString());
guest.setFirstname(map.get("firstname").toString());
guest.setLastname(map.get("lastname").toString());
guest.setReg_date(DateUtils.utilDateToTimestamp(DateUtils.strToDate(map.get("reg_date").toString())));
guest.setId(Integer.valueOf(map.get("id").toString()));
list.add(guest);
}
return list;
}
}
测试用例
package mysql;
import java.util.Date;
import java.util.List;
import org.junit.Test;
import dao.GuestsDao;
import daoImpl.GuestsDaoImpl;
import entity.Guests;
import util.DateUtils;
public class GuestsTest {
GuestsDao dao = new GuestsDaoImpl();
@Test
public void insert() {
Guests guest = new Guests();
guest.setFirstname("三丰");
guest.setLastname("张");
guest.setEmail("zhang@123.com");
guest.setReg_date(DateUtils.utilDateToTimestamp(new Date()));
dao.insertGuests(guest);
}
@Test
public void update() {
Guests guest = new Guests();
guest.setLastname("独孤");
guest.setId(4);
dao.updateGuestsName(guest);
}
@Test
public void delete() {
dao.deleteGuests(4);
}
@Test
public void selectAll() {
List<Guests> list=dao.selectAllGuests();
for (int i = 0; i < list.size(); i++) {
Guests guest=list.get(i);
System.out.println(guest.toString());
}
}
@Test
public void queryAll() {
List<Guests> list=dao.queryAllGuests();
for (int i = 0; i < list.size(); i++) {
Guests guest=list.get(i);
System.out.println(guest.toString());
}
}
}
参考 https://blog.csdn.net/weixin_41482405/article/details/78872125
https://blog.csdn.net/weixin_38303684/article/details/78263008