Dao模式
Data Access Object（數(shù)據(jù)存取對(duì)象）
組成部分：數(shù)據(jù)庫工具類 實(shí)體類 dao接口（對(duì)應(yīng)每個(gè)表的crud操作） dao實(shí)現(xiàn)類
位于業(yè)務(wù)邏輯和持久化數(shù)據(jù)之間
實(shí)現(xiàn)對(duì)持久化數(shù)據(jù)的訪問
數(shù)據(jù)類工具類
- 位于com.starlink.util 中
- 主要功能實(shí)現(xiàn)jdbc 連接 通用的查詢 通用的增刪改 關(guān)閉連接
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;
// 獲取連接 // 通用查詢 // 通用增刪改 // 關(guān)閉連接
/**
*
*數(shù)據(jù)連接類
*/
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 元素個(gè)數(shù) 和慧瘤？的個(gè)數(shù) 位置對(duì)應(yīng) 個(gè)數(shù)對(duì)應(yīng) 數(shù)據(jù)類型對(duì)應(yīng)
*@return
*/
public static int updateOrInsertOrDelete(String sql,Object...objects){
int row = -1;
try {
connection = getConnection();
pstmt = connection.prepareStatement(sql);
// 填充參數(shù)
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 查詢某一個(gè)表的時(shí)候丈攒，要預(yù)先創(chuàng)建實(shí)體類 實(shí)體類的屬性名和列名保持對(duì)應(yīng)
* @param objects
* @return
*/
public static <T> T queryOne(String sql ,Class clazz,Object...objects){
T obj = null;
try {
connection = getConnection();
pstmt = connection.prepareStatement(sql);
// 填充參數(shù)
for (int i = 0; i < objects.length; i++) {
pstmt.setObject(i+1, objects[i]);
}
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
// 列的個(gè)數(shù)
int columnCount = rsmd.getColumnCount();
if(rs.next()){
// 通過一個(gè)類產(chǎn)生的對(duì)象
obj = (T) clazz.newInstance();
//
for (int i = 1; i <=columnCount; i++) {// 循環(huán)表的列
// 列名
String columnName = rsmd.getColumnName(i);
Field[] fs = clazz.getDeclaredFields();
for(int j = 0 ; j < fs.length; j++){ // 循環(huán)實(shí)體里面的屬性
Field f = fs[j];
f.setAccessible(true);
//f.getName() 屬性名
if (columnName.equals(f.getName())) {// 當(dāng)列名 和屬性相等
f.set(obj, rs.getObject(i));// 把列值賦值給對(duì)象的屬性
}
}
}
}
} 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 空的對(duì)象調(diào)用方法 或者屬性 就會(huì)產(chǎn)生
if (rs!=null) {
rs.close();
}
if (pstmt!=null) {
pstmt.close();
}
if (connection!=null) {
connection.close();
}
} catch (Exception e) {
// TODO: handle exception
}
}
}
- 實(shí)體類 位于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 例如學(xué)生dao接口
public interface SchoolDao {
// 查詢 刪除 修改 添加
/**
* 查詢所有的學(xué)校信息
* @return
*/
public List<School> selectAll();
/**
* 分頁查詢學(xué)校列表
* @param pageNo
* @param pageSize
* @return
*/
public List<School> selectPage(int pageNo,int pageSize);
/**
* 根據(jù)id 刪除學(xué)校信息
* @param id
* @return
*/
public int deleteSchool(int id);
/**
* 添加學(xué)校信息
* @param s
* @return
*/
public int insertSchool(School s);
/**
* 修改學(xué)校信息
* @param s
* @return
*/
public int updateSchool(School s);
}
- dao實(shí)現(xiàn)類
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("中原工學(xué)院分院1");
int row = dao.updateSchool(s1);
if (row>0) {
System.out.println("修改成功");
}
*/
/*
School s2 = new School();
s2.setSname("中原工學(xué)院分院1");
int row = dao.updateSchool(s2);
if (row>0) {
System.out.println("刪除成功");
}
*/
}
}
