1.什么是DbUtils?
DbUtils是apache组织提供的一个开源JDBC工具类库,它是对JDBC的简单封装,可以简化jdbc的代码量,同时也不会影响程序的性能。
2.DbUtils相关的类
QueryRunner:组件的核心工具类,定义了所有与数据库操作的方法(查询、更新)。
--- Int update(Connectionconn, String sql, Object param); 执行更新带一个占位符的sql
--- Int update(Connection conn,String sql, Object… param); 执行更新带多个占位符的sql
--- Int[] batch(Connection conn,String sql, Object[][] params) 批处理
---- Int update( String sql,Object param);
---- Int update( String sql,Object… param);
---- Int[] batch( String sql, Object[][] params);如果调用DbUtils的组件的操作数据库的方法,没有传入连接驱动需要的参数, 那么在实例化QueryRunner对象的时候需要传入数据源对象。
3.DbUtils封装的结果对象
<1>.BeanHandler:查询返回单个对象。
<2>.BeanListHandler:查询返回List集合,集合元素是指定的对象。
<3>.ArrayHandler:查询返回结果记录的每一行都封装为对象数组,即返回:Object[]。
<4>.ArrayListHandler:把查询的每一行都封装为对象数组,再添加到list对象中。
<5>.ScalarHandler:查询返回结果记录的第一行的第一列(在聚合函数统计的时候使用)。
<6>.MapHandler:查询返回结果的第一条记录封装为map。
4.实现
查询数据库单条记录,自定义结果集封装数据
工具类:
public class JdbcUtil {
private static String url=null;
private static String username=null;
private static String password=null;
private static String driverClass=null;
static{
try{
Properties props=new Properties();
FileInputStream fis=new FileInputStream("./src/db.properties");
props.load(fis);
url=props.getProperty("url");
username=props.getProperty("username");
password=props.getProperty("password");
driverClass=props.getProperty("driverClass");
//注册驱动程序
Class.forName(driverClass);
}catch(Exception e){
e.printStackTrace();
System.out.println("驱动程序注册失败!");
}
}
//获取对象的连接
public static Connection getConnection(){
try {
Connection conn=DriverManager.getConnection(url, username, password);
return conn;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
}
}
//释放对象的连接
public static void close(Connection conn,Statement stmt,ResultSet rs){
if(rs !=null){
try{
rs.close();
}catch(SQLException e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(stmt !=null){
try{
stmt.close();
}catch(SQLException e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn !=null){
try{
conn.close();
}catch(SQLException e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
public class User {
private Integer id;
private String username;
private String gender;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", gender="
+ gender + "]";
}
}
public class DbUtilsQuery {
private Connection conn;
//查询,自定义结果集封装数据
@Test
public void queryUsingResultSetHandler() throws Exception{
String sql="select * from myuser where id=?";
conn=JdbcUtil.getConnection();
QueryRunner qr=new QueryRunner();
User queryUser=qr.query(conn, sql, new ResultSetHandler<User>(){
@Override
public User handle(ResultSet rs) throws SQLException {
// TODO Auto-generated method stub
if(rs.next()){
User user=new User();
user.setUsername(rs.getString("username"));
user.setGender(rs.getString("gender"));
user.setId(rs.getInt("id"));
return user;
}
return null;
}
},11);
System.out.println(queryUser);
conn.close();
}
}
结果:
查询数据库单条记录,返回对象模型
public class DbUtilsQuery {
private Connection conn;
@Test
public void queryUsingBeanHandler() throws Exception{
String sql="select * from myuser where id=?";
conn=JdbcUtil.getConnection();
QueryRunner qr=new QueryRunner();
User user=qr.query(conn, sql, new BeanHandler<User>(User.class),11);
System.out.println(user);
conn.close();
}
}
结果:
查询数据库多条记录
public class DbUtilsQuery {
private Connection conn;
@Test
public void queryUsingBeanListHandler() throws Exception{
String sql="select * from myuser";
conn=JdbcUtil.getConnection();
QueryRunner qr=new QueryRunner();
List<User> list=qr.query(conn, sql, new BeanListHandler<User>(User.class));
System.out.println(list);
conn.close();
}
}
结果:
[User [id=3, username=Bryant3, gender=男], User [id=4, username=Bryant4, gender=男], User [id=5, username=Bryant5, gender=男], User [id=6, username=Bryant6, gender=男], User [id=7, username=Bryant7, gender=男], User [id=8, username=Bryant8, gender=男], User [id=9, username=Bryant9, gender=男], User [id=11, username=Bryant11, gender=男]]
删除单条记录
public class DbUtilsQuery {
@Test
public void delete() throws Exception{
String sql="delete from myuser where id=11";
Connection conn=JdbcUtil.getConnection();
QueryRunner qr=new QueryRunner();
int rowCount=qr.update(conn, sql);
System.out.println(rowCount);
conn.close();
}
}
结果:
批处理插入多条数据
public class DbUtilsQuery {
@Test
public void insertBatch() throws Exception {
String sql = "insert into myuser (id,username, gender) values(?,?,?)";
Connection conn = JdbcUtil.getConnection();
QueryRunner qr = new QueryRunner();
qr.batch(conn, sql, new Object[][]{ {10,"张三","男"},{11,"李四","女"},{12,"王五","男"}});
conn.close();
}
}
结果:
插入单条记录
实现:
//单条插入
@Test
public void insertOne() throws Exception{
String sql="insert into myuser (id,username,gender) values(?,?,?)";
Connection conn=JdbcUtil.getConnection();
QueryRunner qr=new QueryRunner();
Object[] param={13,"赵六","男"};
qr.update(conn, sql, param);
conn.close();
}
结果:
更新其中任意一条记录的字段
@Test
public void updateOne() throws Exception{
String sql="update myuser set username=? where id=?";
Connection conn=JdbcUtil.getConnection();
QueryRunner qr=new QueryRunner();
Object[] params={"李琦",13};
qr.update(conn, sql, params);
conn.close();
}
结果: