Dao模式
Data Access Object（数据存取对象）
组成部分：数据库工具类 实体类 dao接口（对应每个表的crud操作） dao实现类
位于业务逻辑和持久化数据之间
实现对持久化数据的访问
数据类工具类
- 位于com.starlink.util 中
- 主要功能实现jdbc 连接 通用的查询 通用的增删改 关闭连接
public class DbUtil {
private static final String URL = "jdbc:mysql://localhost:3306/school";
private static final String USER = "root";
private static final String PASSWORD = "root";
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static Connection connection = null;
private static PreparedStatement pstmt = null;
private static ResultSet rs = null;
// 获取连接 // 通用查询 // 通用增删改 // 关闭连接
/**
*
*数据连接类
*/
public static Connection getConnection(){
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
/**
*通用的增删改方法
*@param sql 可以支持 insert update delete
*@param objects 元素个数 和？的个数 位置对应 个数对应 数据类型对应
*@return
*/
public static int updateOrInsertOrDelete(String sql,Object...objects){
int row = -1;
try {
connection = getConnection();
pstmt = connection.prepareStatement(sql);
// 填充参数
for (int i = 0; i < objects.length; i++) {
pstmt.setObject(i+1, objects[i]);
}
row = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
closeAll();
}
return row;
}
/**
* 通用的查询 只能查询的是单条记录
* @param sql sql 只能查询出来一条行记录
* @param clazz 查询某一个表的时候，要预先创建实体类 实体类的属性名和列名保持对应
* @param objects
* @return
*/
public static <T> T queryOne(String sql ,Class clazz,Object...objects){
T obj = null;
try {
connection = getConnection();
pstmt = connection.prepareStatement(sql);
// 填充参数
for (int i = 0; i < objects.length; i++) {
pstmt.setObject(i+1, objects[i]);
}
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
// 列的个数
int columnCount = rsmd.getColumnCount();
if(rs.next()){
// 通过一个类产生的对象
obj = (T) clazz.newInstance();
//
for (int i = 1; i <=columnCount; i++) {// 循环表的列
// 列名
String columnName = rsmd.getColumnName(i);
Field[] fs = clazz.getDeclaredFields();
for(int j = 0 ; j < fs.length; j++){ // 循环实体里面的属性
Field f = fs[j];
f.setAccessible(true);
//f.getName() 属性名
if (columnName.equals(f.getName())) {// 当列名 和属性相等
f.set(obj, rs.getObject(i));// 把列值赋值给对象的属性
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}finally {
closeAll();
}
return obj;
}
/**
* 查询多条记录
* @param sql
* @param clazz
* @param objects
* @return
*/
public static <T> List<T> queryMany(String sql,Class clazz,Object...objects){
List<T> list = new ArrayList<T>();
try {
connection = getConnection();
pstmt = connection.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
pstmt.setObject(i+1, objects[i]);
}
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
while(rs.next()){
T obj = (T) clazz.newInstance();
for (int i = 1; i <=count; i++) {
// 列名
String columnName = rsmd.getColumnName(i);
Field[] fs = clazz.getDeclaredFields();
for (int j = 0; j < fs.length; j++) {
Field f = fs[j];
f.setAccessible(true);
if (columnName.equals(f.getName())) {
f.set(obj, rs.getObject(i));
}
}
}
list.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
closeAll();
}
return list;
}
/**
* 释放资源
*/
public static void closeAll(){
try {
// nullpointException 空的对象调用方法 或者属性 就会产生
if (rs!=null) {
rs.close();
}
if (pstmt!=null) {
pstmt.close();
}
if (connection!=null) {
connection.close();
}
} catch (Exception e) {
// TODO: handle exception
}
}
}
- 实体类 位于com.starlink.entity
public class School {
private int schoolId;
private String sname;
public int getSchoolId() {
return schoolId;
}
public void setSchoolId(int schoolId) {
this.schoolId = schoolId;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
}
- dao接口 位于com.starlink.dao 例如学生dao接口
public interface SchoolDao {
// 查询 删除 修改 添加
/**
* 查询所有的学校信息
* @return
*/
public List<School> selectAll();
/**
* 分页查询学校列表
* @param pageNo
* @param pageSize
* @return
*/
public List<School> selectPage(int pageNo,int pageSize);
/**
* 根据id 删除学校信息
* @param id
* @return
*/
public int deleteSchool(int id);
/**
* 添加学校信息
* @param s
* @return
*/
public int insertSchool(School s);
/**
* 修改学校信息
* @param s
* @return
*/
public int updateSchool(School s);
}
- dao实现类
public class SchoolDaoImpl implements SchoolDao {
@Override
public List<School> selectAll() {
String sql = "select * from school";
List<School> list = DbUtil.queryMany(sql, School.class);
return list;
}
@Override
public List<School> selectPage(int pageNo, int pageSize) {
String sql = "select * from school limit ?,?";
List<School> list = DbUtil.queryMany(sql, School.class,(pageNo-1)*pageSize,pageSize);
return list;
}
@Override
public int deleteSchool(int id) {
String sql = "delete from school where schoolid = ?";
int row = DbUtil.updateOrInsertOrDelete(sql, id);
return row;
}
@Override
public int insertSchool(School s) {
String sql = "insert into school(sname) values(?)";
int row = DbUtil.updateOrInsertOrDelete(sql, s.getSname());
return row;
}
@Override
public int updateSchool(School s) {
String sql = "update school set sname =? where schoolId =?";
int row = DbUtil.updateOrInsertOrDelete(sql, s.getSname(),s.getSchoolId());
return row;
}
}
- 测试类
public class Main {
public static void main(String[] args) {
SchoolDao dao = new SchoolDaoImpl();
// 分页查询
List<School> list1 = dao.selectPage(1, 5);
for (School s : list1) {
System.out.println(s.getSchoolId()+" "+s.getSname());
}
/*
School s1 = new School();
s1.setSchoolId(4);
s1.setSname("中原工学院分院1");
int row = dao.updateSchool(s1);
if (row>0) {
System.out.println("修改成功");
}
*/
/*
School s2 = new School();
s2.setSname("中原工学院分院1");
int row = dao.updateSchool(s2);
if (row>0) {
System.out.println("删除成功");
}
*/
}
}
