元数据的使用
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<size;i++){
//取得每列的列名
String columnName = rsmd.getColumnName(i+1); //从 1 开始的
//取得每列的类型
int typeCode = rsmd.getColumnType(i+1);
System.out.println(columnName+":"+typeCode);
}
/*取得有关SQL的相关元数据
String sql = "insert into user(username,password,birthday,salary) values(?,?,?,?)";
ParameterMetaData psmd = pstmt.getParameterMetaData();
//得到sql语句有多少个参数
int size = psmd.getParameterCount();
System.out.println("共有" + size+"个参数");
*/
/*取得DataBaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
String driver = dbmd.getDriverName(); //驱动路径
String url = dbmd.getURL(); //connect连接的参数,包括协议,用户名和密码
int level = dbmd.getDefaultTransactionIsolation(); 隔离等级
String productName = dbmd.getDatabaseProductName(); 返回产品名称 MySql
boolean flag = dbmd.isReadOnly(); 判断是不是只读,有一些数据库是只能读取,也就是说只能select。。。
System.out.println("flag="+flag);
System.out.println("driver="+driver);
System.out.println("url="+url);
System.out.println("level="+level);
System.out.println("productName="+productName);
*/
}
}
使用元数据+反射优化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<size;i++){
pstmt.setObject(i+1,params[i]);
}
ResultSet rs = pstmt.executeQuery();
if(rs.next()){
//取得结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
//取得结果集列数目
size = rsmd.getColumnCount();
//以列值为单位,设置到JavaBean中去
for(int i=0;i<size;i++){
//取得列名
String columnName = rsmd.getColumnName(i+1);
//通过BeanUtils框架为JavaBean设置值
BeanUtils.setProperty(obj,columnName,rs.getObject(i+1)); //需要导入两个jar包
}
}
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
return obj;
}
//CUD操作(通用的方法)
public static void update(String sql,Object[] params) throws SQLException {
Connection conn = JdbcUtil.getMySqlConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
//取得参数元数据
ParameterMetaData psmd = pstmt.getParameterMetaData();
//取得参数的个数
//正常情况下也是可以使用数据的获取长度方法而不使用元数据类的,但是如果用户无聊在数据多输入几个空格,那么获取的长度就不一致了
//Object[] params = {"sisi","000000","2011-10-28",5000,"","",""};
int size = psmd.getParameterCount();
//循环绑定对象的值
for(int i=0;i<size;i++){
pstmt.setObject(i+1,params[i]);
}
//执行
pstmt.executeUpdate();
//关闭对应的连接对象
JdbcUtil.close(conn);
JdbcUtil.close(pstmt);
}
}
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";
List<User> userList = (List<User>) 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<Object[]> list = (List<Object[]>) runner.query(sql,new ArrayListHandler());
for(Object[] array : list){
System.out.print("编号 : " + array[0] + "\t");
System.out.println("用户名 : " + array[1]);
}
}
@Test
public void testMapHandler() throws SQLException{//存放在一个Map集合里面
QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
String sql = "select * from user";
Map<Object,Object> map = (Map<Object, Object>) runner.query(sql,new MapHandler());
System.out.println("用户名:" + map.get("username"));
}
@Test
public void testMapListHandler() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
String sql = "select * from user";
List<Map<Object,Object>> list = (List<Map<Object, Object>>) runner.query(sql,new MapListHandler());
for(Map<Object,Object> map : list){
System.out.println("用户名:" + map.get("username"));
System.out.println("薪水:" + map.get("salary"));
}
}
@Test
public void testScalarHandler() throws SQLException{ //特殊的获取数据方式
QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
String sql = "select count(*) from user";
Long sum = (Long) runner.query(sql,new ScalarHandler());
System.out.println("共有" + sum + "人");
}
}