DbUtils操作数据库
时间:2022-03-13 23:30
1.什么是O-R Mapping(对象-关系映射)
常用O-R Mapping映射工具
Hibernate(全自动框架)
Ibatis(半自动框架/SQL)
Commons DbUti ls(只是对JDBC简单封装)
还有JPA等之类的,这个不是特别了解,到目前为止也就接触了Hibernate和DbUtils,Hiabernate给人的不用写SQl语句,直接用配置文件去映射关系,DuUtils仍然要写sql语句,他只不过简化了crud的操作(个人看法)
2.dbutils的介绍
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,它是对JDBC的简单封装,学习成本极低,package cn.wwh.www.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.wwh.www.web.jdbc.domain.User;
import cn.wwh.www.web.jdbc.util.JdbcUtils;
/**
*类的作用: ResultSetHandler接口的各种实现类的简单用法
*
*@author 一叶扁舟
*@version 1.0
*@创建时间: 2014-9-6 下午04:16:43
*/
public class Demo4 {
@Test
public void testBeanHandler() throws SQLException {
QueryRunner run = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from UserInfo";
User user = run.query(sql, new BeanHandler(User.class));
System.out.println("beanHandler" + user.toString());
}
@Test
public void testBeanListHandler() throws SQLException {
QueryRunner run = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from UserInfo";
List<User> users = run.query(sql, new BeanListHandler(User.class));
for (User user : users) {
System.out.println(user.toString());
System.out.println();
}
}
@Test
public void testArrayHandler() throws SQLException {
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from userInfo";
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(JdbcUtils.getDataSource());
String sql = "select * from userInfo";
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 {
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from userInfo";
Map<String, Object> map = runner.query(sql, new MapHandler());
System.out.println("用户名:" + map.get("username"));
}
@Test
public void testMapListHandler() throws SQLException {
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from userInfo";
List<Map<String, Object>> list = runner
.query(sql, new MapListHandler());
for (Map<String, 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(JdbcUtils.getDataSource());
String sql = "select count(*) from userInfo";
Long sum = (Long) runner.query(sql, new ScalarHandler());
System.out.println("共有" + sum + "人");
}
}