java 数据库工具,java数据库工具--DBUtils

dome gitub :https://github.com/shimingda/java-dome

概述

DBUtils是Java编程中的数据库操作实用工具，小巧简单实用。

DBUtils封装了对JDBC的操作，简化了JDBC操作，可以少写代码。

DBUtils三个核心功能介绍

QueryRunner中提供对sql语句操作的API

ResultSetHandler接口，用于定义select操作后，怎样封装结果集

DBUtils类，它就是一个工具类，定义了关闭资源与事务处理的方法

QueryRunner核心类

QueryRunner(DataSourcr ds),提供数据源(连接池)，DbUtils底层自动维护连接connection

update(String sql，Obj…params)，执行更新数据

query(String sql，ResultSetHandlerrsh,Object…panrams)，执行查询

ResultSetHandler结果集处理类

ArrayHandler：适合取1条记录，把结果集中的第一行数据转成对象数组。

ArrayListHandler：适合取多条记录，把结果集中的每一行数据都转成一个对象数组，再存放到List中。

BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中(把每条记录封装成对象，适合取一条记录)

BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。//重点

MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。//重点

MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List

ColumnListHandler：将结果集中某一列的数据存放到List中。

KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里(List

ScalarHandler:将结果集第一行的某一列放到某个对象中。//重点

基础

package dome.base;

import org.apache.commons.dbcp2.BasicDataSource;

import javax.sql.DataSource;

import java.sql.Connection;

import java.sql.DriverManager;

/**

* 数据配置

* @author Somin

*/

public class DBConf

{

static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";

static final String DB_URL = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf-8";

static final String USER = "root";

static final String PASS = "1234";

public static Connection getConnection(){

try

{

Class.forName(DBConf.JDBC_DRIVER);

return DriverManager.getConnection(DBConf.DB_URL, DBConf.USER, DBConf.PASS);

}

catch (Exception e)

{

System.out.println("databases connection error");

return null;

}

}

public static DataSource getDataSource()

{

BasicDataSource basicDataSource=new BasicDataSource();

basicDataSource.setDriverClassName(JDBC_DRIVER);

basicDataSource.setUsername(USER);

basicDataSource.setPassword(PASS);

basicDataSource.setUrl(DB_URL);

return basicDataSource;

}

}

package dome.base;

/**

* 数据库实体类

* @author Somin

*/

public class User

{

public int id;

public String name;

public int age;

public int getId()

{

return id;

}

public void setId(int id)

{

this.id = id;

}

public String getName()

{

return name;

}

public void setName(String name)

{

this.name = name;

}

public int getAge()

{

return age;

}

public void setAge(int age)

{

this.age = age;

}

}

import dome.base.DBConf;

import dome.base.User;

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.ResultSetHandler;

import org.apache.commons.dbutils.handlers.*;

import org.junit.Before;

import org.junit.Ignore;

import org.junit.Test;

import java.sql.Connection;

import java.util.Arrays;

import java.util.List;

import java.util.Map;

/**

* base test

* @author Somin

*/

public class BaseTest

{

QueryRunner queryRunner = new QueryRunner();

Connection conn= null;

@Before

public void init()

{

conn= DBConf.getConnection();

}

@Test

@Ignore

public void insertTest() throws Exception

{

String sql = "INSERT INTO user(name, age) VALUES(?,?)";

Object[] objectArr= queryRunner.insert(conn, sql, new ArrayHandler(), "root", 18);

System.out.println("数组长度：" + objectArr.length + "；第0个元素的值：" +objectArr[0]);

objectArr = queryRunner.insert(conn, sql, new ArrayHandler(), "test", 19);

System.out.println("数组长度：" + objectArr.length + "；第0个元素的值：" +objectArr[0]);

}

@Test

@Ignore

public void queryBeanTest() throws Exception

{

ResultSetHandlerresultSetHandler=new BeanHandler(User.class);

String sql ="SELECT * FROM user where id =?";

User user=queryRunner.query(conn,sql,resultSetHandler,1);

System.out.println("ID:"+user.getId()+",name:"+user.getName());

ResultSetHandler> resultSetHandlers=new BeanListHandler(User.class);

String sqlList="SELECT * FROM user";

Listusers=queryRunner.query(conn,sqlList,resultSetHandlers);

users.forEach(u->{

System.out.println("id:"+u.getId()+",name:"+u.getName());

});

}

@Test

public void queryMapTest() throws Exception

{

String sql ="SELECT * FROM user where id =?";

MapresultMap = queryRunner.query(conn,sql,new MapHandler(),1);

System.out.println("获取一条数据："+resultMap);

String sql2 ="SELECT * FROM user ";

List> resultMaps = queryRunner.query(conn,sql2,new MapListHandler());

resultMaps.forEach(result->{

System.out.println(result);

});

}

@Test

@Ignore

public void queryListTest() throws Exception

{

String sql="SELECT * FROM user";

Listobjects= queryRunner.query(conn,sql,new ArrayListHandler());

objects.forEach(object->{

System.out.println(Arrays.toString(object));

});

}

}

进阶

package dome.advanced;

import dome.base.User;

import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.ResultSet;

import java.sql.SQLException;

/**

* 自定义映射

* @author Somin

*/

public class UserHandler extends BeanHandler{

public UserHandler()

{

super(User.class);

}

@Override

public User handle(ResultSet rs) throws SQLException

{

return super.handle(rs);

}

}

import dome.advanced.UserHandler;

import dome.base.DBConf;

import dome.base.User;

import org.apache.commons.dbutils.QueryRunner;

import org.junit.Ignore;

import org.junit.Test;

import java.sql.Connection;

/**

* advanced test

* @author Somin

*/

public class AdvancedTest

{

@Test

@Ignore

public void handlerTest() throws Exception

{

QueryRunner queryRunner = new QueryRunner();

Connection conn=DBConf.getConnection();

String sql ="SELECT * FROM user where id =?";

User user=queryRunner.query(conn,sql,new UserHandler(),1);

System.out.println("id:"+user.getId()+",name:"+user.getName());

}

@Test

public void dataSourceTest() throws Exception

{

QueryRunner queryRunner=new QueryRunner(DBConf.getDataSource());

String sql ="SELECT * FROM user where id =?";

User user=queryRunner.query(sql,new UserHandler(),1);

System.out.println("id:"+user.getId()+",name:"+user.getName());

}

}