心得3--JDBC增删改查模版编写与案例

1. 元数据－ DataBaseMetaData元数据：数据库、表、列的定义信息。Connection.getDatabaseMetaData()DataBaseMetaData对象getURL()：返回一个String类对象，代表数据库的URL。getUserName()：返回连接当前数据库管理系统的用户名。getDatabaseProductName()：返回数据库的产品名称。getDatabaseProductVersion()：返回数据库的版本号。getDriverName()：返回驱动驱动程序的名称。getDriverVersion()：返回驱动程序的版本号。isReadOnly()：返回一个boolean值，指示数据库是否只允许读操作。2. 元数据－ ParameterMetaData PreparedStatement . getParameterMetaData() •获得代表PreparedStatement元数据的ParameterMetaData对象。 •Select * from user where name=? And password=?ParameterMetaData对象•getParameterCount() •获得指定参数的个数•getParameterType(int param) •获得指定参数的sql类型3. 元数据－ ResultSetMetaData ResultSet. getMetaData() 获得代表ResultSet对象元数据的ResultSetMetaData对象。 ResultSetMetaData对象•getColumnCount() ：返回resultset对象的列数•getColumnName(int column) ：获得指定列的名称•getColumnTypeName(int column)：获得指定列的类型 4.重点内容：使用元数据简化JDBC代码业务背景：系统中所有实体对象都涉及到基本的CRUD操作：所有实体的CUD操作代码基本相同，仅仅发送给数据库的SQL语句不同而已，因此可以把CUD操作的所有相同代码抽取到工具类的一个update方法中，并定义参数接收变化的SQL语句。实体的R操作，除SQL语句不同之外，根据操作的实体不同，对ResultSet的映射也各不相同，因此可义一个query方法，除以参数形式接收变化的SQL语句外，可以使用策略模式由qurey方法的调用者决定如何把ResultSet中的数据映射到实体对象中。5． 以上知识点的案例分析DatabaseMetaData类package com.csdn.demo;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.SQLException;import com.csdn.uil.DBManager_c3p0;public class Demo3 {private static Connection con = null;public static void main(String[] args) {try {con = DBManager_c3p0.getCon();DatabaseMetaData dbmd = con.getMetaData();System.out.println(dbmd.getURL());System.out.println(dbmd.getDriverName());System.out.println(dbmd.getDriverVersion());System.out.println(dbmd.getDatabaseProductName());System.out.println(dbmd.getDatabaseProductVersion());} catch (SQLException e) {e.printStackTrace();}}}ParameterMetaData类package com.csdn.demo;import java.sql.Connection;import java.sql.ParameterMetaData;import java.sql.PreparedStatement;import java.sql.SQLException;import com.csdn.uil.DBManager_c3p0;public class Demo4 {/** * @param args * mysql驱动对于ParameterMetaData类的某些方法不支持，就是访问属性的某些方法，oracle支持 */private static Connection con = null;private static PreparedStatement ps = null;public static void main(String[] args) {try {con = DBManager_c3p0.getCon();String sql = "select * from user where id = ?";ps = con.prepareStatement(sql);ParameterMetaData pm = ps.getParameterMetaData();System.out.println(pm.getParameterCount());} catch (SQLException e) {e.printStackTrace();}}}ResultSetMetaData类package com.csdn.demo;import java.sql.Connection;import java.sql.ParameterMetaData;import java.sql.PreparedStatement;import java.sql.ResultSetMetaData;import java.sql.SQLException;import com.csdn.uil.DBManager_c3p0;public class Demo5 {/** * @param args */private static Connection con = null;private static PreparedStatement ps = null;public static void main(String[] args) {try {con = DBManager_c3p0.getCon();String sql = "select * from user where id = ?";ps = con.prepareStatement(sql);ResultSetMetaData rs = ps.getMetaData();System.out.println(rs.getColumnClassName(1));System.out.println(rs.getCatalogName(1));int count = rs.getColumnCount();for(int i=0;i<count;i++){System.out.println(rs.getColumnName(1));System.out.println(rs.getColumnType(1));System.out.println(rs.getColumnTypeName(1));}} catch (SQLException e) {e.printStackTrace();}}}DBManager_c3p0(连接池)类package com.csdn.uil;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import com.mchange.v2.c3p0.ComboPooledDataSource;public class DBManager_c3p0 {private static ComboPooledDataSource ds = null;static{//这里要注意的是：xml配置文件的名字一定要是c3p0-config.xml，可以doc参考帮助，帮助中有命名，如果名字写错了会无法加载驱动，空指针的错ds = new ComboPooledDataSource("mysql");/*try {ds =new ComboPooledDataSource();ds.setDriverClass("com.mysql.jdbc.Driver");//自己写的时候一定要区分com.mysql.jdbc和jdbc:mysql:的顺序区别，写错之后会出无法加载driver驱动的错ds.setJdbcUrl("jdbc:mysql://localhost:3306/login");ds.setUser("root");ds.setPassword("root");ds.setInitialPoolSize(10);ds.setMaxPoolSize(20);ds.setMinPoolSize(5);} catch (PropertyVetoException e) {e.printStackTrace();}*/}    public static Connection getCon() throws SQLException{    return ds.getConnection();    }    public static void closeDB(Connection con,PreparedStatement ps,ResultSet rs){    if(rs!=null){    try {rs.close();} catch (SQLException e) {e.printStackTrace();}    }    if(ps!=null){    try {    ps.close();    } catch (SQLException e) {    e.printStackTrace();    }    }    if(con!=null){    try {    con.close();    } catch (SQLException e) {    e.printStackTrace();    }    }    }    //CUD增删改模版    public static void update(String sql,Object[] params){    Connection con = null;    PreparedStatement ps = null;    try {con = DBManager_c3p0.getCon();ps = con.prepareStatement(sql);for(int i=0;i<params.length;i++){ps.setObject(i+1,params[i]);}int i = ps.executeUpdate();if(i>0){System.out.println("成功了！！");}} catch (SQLException e) {e.printStackTrace();}finally{DBManager_c3p0.closeDB(con,ps,null);}    }    public static Object query(String sql,Object[] params,ResultSetHandler rsh){    Connection con = null;    PreparedStatement ps = null;    ResultSet rs = null;    try {con = DBManager_c3p0.getCon();ps = con.prepareStatement(sql);for(int i=0;i<params.length;i++){ps.setObject(i+1,params[i]);}rs = ps.executeQuery();return rsh.handler(rs);} catch (SQLException e) {throw new RuntimeException(e);}finally{DBManager_c3p0.closeDB(con,ps,null);}    }}操作实现类：BeanHandler类package com.csdn.uil;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.lang.reflect.Field ;public class BeanHandler implements ResultSetHandler {private Class clazz;public BeanHandler(Class clazz) {super();this.clazz = clazz;}@Overridepublic Object handler(ResultSet rs) {try {if(!rs.next()){return null;}Object bean = clazz.newInstance();ResultSetMetaData rm = rs.getMetaData();int count = rm.getColumnCount();for(int i=0;i<count;i++){String columnName = rm.getColumnName(i+1);Object value = rs.getObject(columnName);Field f = bean.getClass().getDeclaredField(columnName);f.setAccessible(true);f.set(bean,value);}return bean;} catch (Exception e) {throw new RuntimeException();}}}实现类：Model类package com.csdn.model;import org.junit.Test;import com.csdn.domain.users;import com.csdn.uil.BeanHandler;import com.csdn.uil.DBManager_c3p0;public class Model {@Testpublic void insert(){String sql = "insert into user (username,password) values(?,?)";Object[] params = {"aa","aa"};DBManager_c3p0.update(sql, params);}@Testpublic void delete(){String sql = "delete from user where id = ?";Object[] params = {18};DBManager_c3p0.update(sql, params);}@Testpublic void update(){String sql = "update user set username = ?,password = ? where id = ?";Object[] params = {"bb","bb",17};DBManager_c3p0.update(sql, params);}@Testpublic void find(){String sql = "select id,username,password from user where id = ?";Object[] params = {1};users users = (users)DBManager_c3p0.query(sql, params, new BeanHandler(users.class));System.out.println(users.getId()+";"+users.getUsername()+";"+users.getPassword());}@Testpublic void findAll(){String sql = "select id,username,password from user";//因为做的处理器有参数，所以即使没有置换参数也要创建一个空数组传参数Object[] params = {};List<Users> list = (List<Users>)DBManager_c3p0.query(sql, params, new ListHandler(Users.class));System.out.println(list);//输出一个对象System.out.println(list.size());for(Users user : list){System.out.println(user.getId()+";"+user.getUsername()+";"+user.getPassword());}}}返回list的查询模版package com.csdn.uil;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.util.ArrayList;import java.util.List;import java.lang.reflect.Field;public class ListHandler implements ResultSetHandler{private Class clazz;public ListHandler(Class clazz) {super();this.clazz = clazz;}@Overridepublic Object handler(ResultSet rs) {try {List list = new ArrayList();while(rs.next()){Object bean = clazz.newInstance();ResultSetMetaData meta = rs.getMetaData();int count = meta.getColumnCount();for(int i=0;i<count;i++){String columnName = meta.getColumnName(i+1);Object columnValue = rs.getObject(columnName);//这里是获取bean里的字段，所以要用bean.getClass()Field field = bean.getClass().getDeclaredField(columnName);field.setAccessible(true);field.set(bean, columnValue);}list.add(bean);}return list;} catch (Exception e) {throw new RuntimeException(e);}}}