DbUtils
pom.xml
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>5.4.0</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.14</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-nop</artifactId>
<version>1.7.26</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db1?serverTimezone=Asia/Shanghai
username=root
password=root
#初始化连接数量
initialSize=5
#最大连接数
maxActive=10
#最大等待时间
maxWait=3000
DruidTest.java
public class DruidTest {
public static void main(String[] args) throws Exception {
//1.加载配置文件
Properties pro = new Properties();
InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);
//2.获取连接对象
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
//3.获取连接
Connection conn = ds.getConnection();
System.out.println(conn);
}
}
JDBCUtils.java
/*
Druid连接池的工具类
*/
public class JDBCUtils {
//1.定义成员变量 DataSource
private static DataSource ds;
static {
//1.加载配置文件
Properties pro = new Properties();
try {
pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
//获取DataSource
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
//释放资源
public static void close(Statement stmt,Connection conn){
if (stmt!=null) {
try {
stmt.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
if (conn!=null){
try {
conn.close();//归还连接
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
public static void close(ResultSet rs, Statement stmt, Connection conn){
if (rs!=null) {
try {
rs.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
if (stmt!=null) {
try {
stmt.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
if (conn!=null){
try {
conn.close();//归还连接
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
//获取连接池
public static DataSource getDataSource(){
return ds;
}
}
DBUtils:是JDBC的简化开发工具包,小巧而简单实用。
DBUtils三个核心功能介绍:
QueryRunner中提供对sql语句操作的API
ResultSetHandler接口,用于定义select操作后,怎样封装结果集
DbUtils类,它就是一个工具类,定义了关闭资源与事务处理的方法
1.QueryRunnerTest.java
/*
使用QueryRunner类,实现对数据表的
insert delete update
调用QueryRunner类的方法update(Connection con,String sql,Object...param)
Object...param可变参数,Object类型,SQL语句可以出现?占位符
数据库的连接对象需要传递,由自定义的工具类传递
*/
public class QueryRunnerTest {
private static Connection con;
static {
try {
con = JDBCUtils.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception {
//insert();
//update();
delete();
}
/*
定义方法,使用QueryRunner类的方法update向数据库中添加数据
*/
public static void insert() throws SQLException {
//创建QueryRunner类对象
QueryRunner qr = new QueryRunner();
//定义sql语句
String sql = "insert into goods values(?,?,?,?)";
//将三个占位符的实际参数写到数组中
Object[] params = {8,"可乐",5.5,1};
//调用QueryRunner类的方法update执行SQL语句
int n = qr.update(con,sql,params);
System.out.println(n);
DbUtils.closeQuietly(con);//安静的关闭 即异常它已经给你处理了
}
/*
定义方法,使用QueryRunner类的方法update将数据表的数据修改
*/
public static void update() throws Exception{
//创建QueryRunner类对象
QueryRunner qr = new QueryRunner();
//书写sql语句
String sql = "update goods set goodsname=? where goodsid=?";
//将?占位符的数据写入数组中
Object[] params = {"芬达",8};
//执行操作
int n = qr.update(con,sql,params);
System.out.println(n);
DbUtils.closeQuietly(con);//安静的关闭 它把异常已经处理了
}
/*
定义方法,使用QueryRunner类的方法update将表的数据删除
*/
public static void delete() throws SQLException {
//获取QueryRunner对象
QueryRunner qr = new QueryRunner();
//书写sql语句
String sql = "delete from goods where goodsid=?";
//给?占位符赋值并写入数组
Object[] params = {8};
//调用delete方法
int n = qr.update(con,sql,params);
System.out.println(n);
DbUtils.closeQuietly(con);
}
}
2.
/*
QueryRunner数据查询操作:
调用QueryRunner类方法query(Connection con,String sql,ResultSetHandler r,Object...params)
ResultSetHandler r 结果集的处理方式,传递ResultSetHandler接口实现类
Object...params SQL语句中的?占位符
注意:query方法返回值都是泛型,具体返回类型由结果集的处理方式决定
*/
public class QueryRunnerTest2 {
private static Connection con;
static {
try {
con = JDBCUtils.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws SQLException {
//arrayHandler();
//arrayListHandler();
//beanHandler();
//beanListHandler();
//columnListHandler();
//scalarHandler();
//mapHandler();
mapListHandler();
}
/*
结果集第一种处理方法,ArrayHandler
将结果集的第一行存储到对象数组中 Object[]
*/
public static void arrayHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "select * from goods";//虽然这里是返回多行,但是结果集只存储第一行
//调用query方法执行查询,传递连接对象,sql语句,结果集处理方式的实现类
Object[] obj = qr.query(con,sql,new ArrayHandler());
for (Object object:obj) {
System.out.print(object+"\t");
}
}
/*
结果集第二种处理方法,ArrayListHandler
将结果集的每一行封装到对象数组中,出现很多对象数组
对象数组存储到List集合中
*/
public static void arrayListHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "select * from goods";
//调用query方法,结果集处理的参数上,传递实现类ArrayListHandler
List<Object[]> list = qr.query(con,sql,new ArrayListHandler());
for (Object[] obj:list) {
for (Object objs:obj) {
System.out.print(objs+"\t");
}
System.out.println();
}
}
/*
结果集的第三种实现方式,BeanHandler
将结果集的第一行数据,封装成JavaBean对象
*/
public static void beanHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "select * from goods";
//调用方法,传递结果集实现类BeanHandler
//BeanHandler(Class<T> type)
GoodsInfo goodsInfo = qr.query(con,sql,new BeanHandler<GoodsInfo>(GoodsInfo.class));
System.out.println(goodsInfo.toString());
}
/*
结果集的第四种处理方法,BeanListHandler
将数据结果集的每一行数据封装成JavaBean对象
多个JavaBean对象封装到List集合中
*/
public static void beanListHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "select * from goods";
List<GoodsInfo> list = qr.query(con,sql,new BeanListHandler<>(GoodsInfo.class));
for (GoodsInfo gi:list) {
System.out.println(gi);
}
}
/*
结果集的第五种处理方法,ColumnListHandler
结果集,指定列的数据,存储到List集合
List<Object> 每个列的数据类型不同
*/
public static void columnListHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql ="select * from goods";
List<Object> list = qr.query(con,sql,new ColumnListHandler<Object>("goodsid"));
for (Object object:list) {
System.out.println(object);
}
}
/*
结果集的第六种处理方法,ScalarHandler
对于查询后,只有一个结果
*/
public static void scalarHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "select count(*) from goods";
Object object = qr.query(con,sql,new ScalarHandler<Object>());
System.out.println(object);
}
/*
结果集的第7种处理方法,MapHandler
将结果集的第一行数封装到Map集合
Map<键,值> 键就是列名,值就是列的数据
*/
public static void mapHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "select * from goods";
//调用query方法,传递结果集实现MapHandler
//返回值:Map集合,Map接口实现类,泛型
Map<String,Object> map = qr.query(con,sql,new MapHandler());
for (String key:map.keySet()) {//无论你查多少行 我只放第一行
System.out.println(key+"\t"+map.get(key));
}
}
/*
结果集的第8种处理方法,MapHandler
将结果集的每一行数封装到Map集合,Map<键,值> 键就是列名,值就是列的数据
然后再将map集合存到List集合
*/
public static void mapListHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "select * from goods";
//调用方法query,传递结果集实现MapListHandler
//返回值List集合,存储的是Map集合
List<Map<String,Object>> list = qr.query(con,sql,new MapListHandler());
for (Map<String,Object> map:list) {
for (String key:map.keySet()) {
System.out.print(key+"\t"+map.get(key)+"\t");
}
System.out.println();
}
}
}