JDBC——DAO设计模式
DAO:Data Access Object
实现功能的模块化。
INSERT UPDATE DELETE
void update(String sql, Object ...args);
package jdbc; 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 com.mysql.jdbc.Connection; import com.mysql.jdbc.PreparedStatement; /** *INSERT UPDATE DELETE *void update(String sql, Object ...args); */ public class DAO { public void update(String sql, Object ...args) { Connection conn = null; PreparedStatement ps = null; ResultSet res = null; try { //1.创建Connection对象 conn = (Connection) JDBCTools.getConnection(); //2.创建PreparedStatement对象 ps = (PreparedStatement) conn.prepareStatement(sql); //3.调用PreparedStatement的setObject()对占位符进行赋值 for(int i = 0; i < args.length; ++i) { ps.setObject(i + 1, args[i]); } ps.executeUpdate(); } catch(Exception e) { e.printStackTrace(); }finally { JDBCTools.release(null, ps, conn); } } }
查询一条记录
<T> T get(class<T> clazz, String sql, Object ... args);
/** *查询一条记录 *<T> T get(class<T> clazz, String sql, Object ... args); */ public class DAO { //查询一条记录,返回对应的对象 public <T> T get(Class<T> clazz, String sql, Object ...args){ T entity = null; Connection conn = null; PreparedStatement ps = null; ResultSet resultSet = null; ResultSetMetaData rsmd = null; try { //1.获取数据库连接 conn = (Connection) JDBCTools.getConnection(); //2.使用PreparedStatement填充sql语句 ps = (PreparedStatement) conn.prepareStatement(sql); //3.填充sql中的占位符 for(int i = 0; i < args.length; ++i) { ps.setObject(i + 1, args[i]); } //4.进行查询得到ResultSet对象 //4.获得ResultSetMetaData resultSet = ps.executeQuery(); //5.准备一个Map<String, Object>:key:存放列的别名 Value:存放列的值 Map<String, Object> map = new HashMap<String, Object>(); //6.处理ResultSet if(resultSet.next()) { //7.得到ResultSetMetaData对象 //6.返回符合条件的对象 rsmd = resultSet.getMetaData(); int columnCount = rsmd.getColumnCount(); //8.由ResultSetMetaData的getColumnCount()得到有多少列,getColumnLabel()得到列的别名 for(int i = 0; i < columnCount; ++i) { String key = rsmd.getColumnLabel(i + 1);//ResultSetMetaData的getColumnLabel()得到列的别名 Object value = resultSet.getObject(i + 1);////ResultSet的getObject()得到列的值 //9.填充Map对象 map.put(key, value); } } //******10.若map不为空集,用反射创建Class对应的对象 entity = clazz.newInstance(); //11.遍历Map对象,利用反射给对象属性赋值 key为属性名 value为属性值 for(Map.Entry<String, Object> entry: map.entrySet()) { String key = entry.getKey(); Object value = entry.getValue(); //利用反射给对象属性赋值 key为属性名 value为属性值 ReflectionTools.setValue(entity, key, value); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { JDBCTools.release(resultSet, ps, conn); } return entity; } }
查询多条记录,返回对象的集合
<T> List<T> getForList(Class<T> clazz, String sql, Object ...args);
/** *查询多条记录,返回对象的集合 *<T> List<T> getForList(Class<T> clazz, String sql, Object ...args); */ public class DAO { //查询多条记录,返回对象的集合 public <T> List<T>getForList(Class<T> clazz, String sql, Object ...args){ List<T> result = new ArrayList(); Connection conn = null; PreparedStatement ps = null; ResultSet resultSet = null; ResultSetMetaData rsmd = null; try { //1. conn = (Connection) JDBCTools.getConnection(); //2. ps = (PreparedStatement) conn.prepareStatement(sql); //3. for(int i = 0; i < args.length; ++i) { ps.setObject(i + 1, args[i]); } //4. resultSet = ps.executeQuery(); //5.准备一个List<Map<String, Object>>: 键:存放列的别名 值:列的值,其中一个Map对应一个记录 List<Map<String, Object>> list = new ArrayList<>(); //6.处理ResultSet结果,用while循环 while(resultSet.next()) { //7.获得ResultSetMetaData对象 rsmd = resultSet.getMetaData(); //8.准备Map<String, Object>用来存放每一条查询的记录 Map<String, Object> map = new HashMap<>(); //9.遍历结果集,将结果存放在Map中 int column = rsmd.getColumnCount(); for(int i = 0; i < column; ++i) { String key = rsmd.getColumnLabel(i + 1); Object value = resultSet.getObject(i + 1); map.put(key, value); } //10.把每条map记录放入List中 list.add(map); } //11.判断List是否为空,若不为空,遍历List得到每个Map对象。再把每个Map对象转为Class对应的Object对象 T bean = null; if(list.size() > 0) { for(Map<String, Object> m : list) { bean = clazz.newInstance(); for(Map.Entry<String, Object> entry: m.entrySet()) { String key = entry.getKey(); Object value = entry.getValue(); ReflectionTools.setValue(bean, key, value); } //12.把Object放入List中 result.add(bean); } } }catch(Exception e) { e.printStackTrace(); } finally { JDBCTools.release(resultSet, ps, conn); } return result; } }
返回某条记录的某一个字段的值或一个统计的值
<E> E getForValue(String sql, Object ...args);
/** *返回某条记录的某一个字段的值或一个统计的值 *<E> E getForValue(String sql, Object ...args); */ public class DAO { //返回某条记录的某一个字段的值或一个统计的值 public <E> E getForValue(String sql, Object ...args) { Connection conn = null; PreparedStatement ps = null; ResultSet resultSet = null; ResultSetMetaData rsmd= null; try { conn = (Connection) JDBCTools.getConnection(); ps = (PreparedStatement) conn.prepareStatement(sql); for(int i = 0; i < args.length; ++i) { ps.setObject(i + 1, args[i]); } resultSet = ps.executeQuery(); if(resultSet.next()) { return (E) resultSet.getObject(1); } }catch(Exception e) { e.printStackTrace(); }finally { JDBCTools.release(resultSet, ps, conn); } return null; } }