元数据的使用
1)想取得对数据库相关信息的描述,可以元数据 2)DatabaseMetaData/DatabaseMetaData dbmd = conn.getMetaData() 3)ParameterMetaData/ParameterMetaData psmd = pstmt.getParameterMetaData(); 4)ResultSetMetaData/ResultSetMetaData rsmd = rs.getMetaData();取得结果集相关的,sql语句相关的,数据库相关的元数据
package cn.itcast.web.jdbc.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import cn.itcast.web.jdbc.util.JdbcUtil;//演示三种元数据库的用法public class Demo1 { public static void main(String[] args) throws Exception { Connection conn = JdbcUtil.getMySqlConnection(); String sql = "select * from user"; PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); //取得结果集的相关元数据 ResultSetMetaData rsmd = rs.getMetaData(); int size = rsmd.getColumnCount(); //获取总共有多少列 for(int i=0;i使用元数据+反射优化CURD操作
package cn.itcast.web.jdbc.dao;import java.sql.Connection;import java.sql.ParameterMetaData;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import org.apache.commons.beanutils.BeanUtils;import cn.itcast.web.jdbc.domain.User;import cn.itcast.web.jdbc.util.JdbcUtil;//使用元数据+反射优化CURD操作public class Demo2 { public static void main(String[] args) throws Exception { //String sql = "insert into user(username,password,birthday,salary) values(?,?,?,?)"; //Object[] params = {"sisi","000000","2011-10-28",5000}; //String sql = "update user set username=? where username=?"; //String sql = "delete from user where id = ?"; //update(sql,new Object[]{2}); String sql = "select * from user where id = ?"; //自动把获取的数据封转到一个javabean对象中去 User user = (User) query(sql,new Object[]{1},User.class); System.out.println("用户名:" + user.getUsername()); System.out.println("密码:" + user.getPassword()); System.out.println("生日:" + user.getBirthday().toLocaleString()); System.out.println("薪水:" + user.getSalary()); } //R操作(通用的方法) public static Object query(String sql,Object[] params,Class clazz) throws Exception { Object obj = clazz.newInstance(); Connection conn = JdbcUtil.getMySqlConnection(); PreparedStatement pstmt = conn.prepareStatement(sql); ParameterMetaData psmd = pstmt.getParameterMetaData(); int size = psmd.getParameterCount(); for(int i=0;i
DBUtils框架的使用:实现原理和自己用元数据封装CRUD操作差不多。
1)目的:减化CURD操作
2)DBUtils框架最核心的类,就是QueryRunner类,构造其有二种方式 a)空参构造 b)通过DataSource构造 3)DBUtils对象的update()方法,内部已经关闭相关的连接对象 4)update(Connection)方法带有Connection对象的,需要手工关闭,其它对象自动关闭 update()方法无Connection对象的,DBUtils框架自动关闭 5)为什么作者要这样设计? 主要考虑了在分层结构中,需要用到同一个Connection的问题 在连接池取connection肯定是随机的,只要能获取到就行了 6)对于query()操作与update()操作有着一致的含义 update是用在delect,update,insert query是用在select演示DBUtils框架的使用
package cn.itcast.web.jdbc.dao;import java.sql.Connection;import java.sql.SQLException;import org.apache.commons.dbutils.QueryRunner;import cn.itcast.web.jdbc.util.JdbcUtil;//演示DBUtils框架的使用public class Demo3 { public static void main(String[] args) throws SQLException { //QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource()); //String sql = "insert into user(username,password,birthday,salary) values(?,?,?,?)"; //String sql = "update user set username=? where username=?"; //String sql = "delete from user where id = ?"; //updata有多个重载方法 //runner.update(sql,new Object[]{4}); Connection conn = JdbcUtil.getMySqlConnection(); //这里是没有参数的,也就是没有带一个数据源,那就需要自己获取一个connection对象 //然后在后面update就要使用到这个connection,而且还要自己关闭connection QueryRunner runner = new QueryRunner(); String sql = "insert into user(username,password,birthday,salary) values(?,?,?,?)"; runner.update(conn,sql,new Object[]{"tim","111222","2011-10-10",5000}); JdbcUtil.close(conn); }}对于query()操作的实现类含义如下 BeanHandler/BeanListHandler:争对JavaBean ArrayHandler/ArrayListHandler:争对数组 MapHandler/MapListHandler:争对Map ScalarHandler:争对Long
package cn.itcast.web.jdbc.dao;import java.sql.SQLException;import java.util.List;import java.util.Map;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.ArrayHandler;import org.apache.commons.dbutils.handlers.ArrayListHandler;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.MapHandler;import org.apache.commons.dbutils.handlers.MapListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import org.junit.Test;import cn.itcast.web.jdbc.domain.User;import cn.itcast.web.jdbc.util.JdbcUtil;//演示ResultSetHandler接口的各种实现类的用法public class Demo4 { @Test public void testBeanHandler() throws SQLException{ //存放在javabean里面 QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource()); String sql = "select * from user where id = ?"; User user = (User) runner.query(sql,1,new BeanHandler(User.class)); //sql语句的参数的传递方法 System.out.println("用户名:" + user.getUsername()); } @Test public void testBeanListHandler() throws SQLException{ QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource()); String sql = "select * from user"; ListuserList = (List ) runner.query(sql,new BeanListHandler(User.class)); for(User user : userList){ System.out.println("用户名:" + user.getUsername()); System.out.println("密码:" + user.getPassword()); } } @Test public void testArrayHandler() throws SQLException{//存放在一个数组里面 QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource()); String sql = "select * from user"; Object[] array = (Object[]) runner.query(sql,new ArrayHandler()); System.out.println("编号 : " + array[0]); System.out.println("用户名 : " + array[1]); } @Test public void testArrayListHandler() throws SQLException{ QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource()); String sql = "select * from user"; List