Java DbUtils实用

凡尔赛mo

已于 2022-11-27 02:15:53 修改

阅读量606

点赞数

分类专栏： Java 开发文章标签： java 数据库 mysql

于 2022-11-27 02:14:06 首次发布

本文链接：https://blog.csdn.net/qq_35131055/article/details/128060070

版权

Java 开发专栏收录该内容

27 篇文章 0 订阅

订阅专栏

     <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>

01.简单的连接

public class QueryRunnerTest {
    // 定义JDBC相关参数
    private static String URL = "jdbc:mysql://216.127.*.*:3166/mall_pms?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false";
    private static String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String USERNAME = "root";
    private static String PASSWORD = "root";
    private static Connection conn;

    
    public static Connection getConnection() {// 用于获得数据库连接的工具方法
        try {
            DbUtils.loadDriver(DRIVER);// 加载驱动
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);// 建立连接
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
     public static void main(String[] args) throws SQLException {
        PreparedStatement preparedStatement = getConnection().prepareStatement("select * from pms_brand where brand_id=1");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            System.err.println(resultSet.getString(2));
        }
    }
}
    
    
    }

02.dbutil

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;

import javax.sound.midi.Soundbank;
import java.sql.*;

public class QueryRunnerTest {
    // 定义JDBC相关参数
    private static String URL = "jdbc:mysql://216.127.*.*:3166/mall_pms?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false";
    private static String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String USERNAME = "root";
    private static String PASSWORD = "root";
    private static Connection conn;

    
    public static Connection getConnection() {// 用于获得数据库连接的工具方法
        try {
            DbUtils.loadDriver(DRIVER);// 加载驱动
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);// 建立连接
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public static int operate(String sql, Object... params) {// 用于执行有参数的SQL语句
        int result = 0;
        QueryRunner runner = new QueryRunner();
        try {
            result = runner.update(getConnection(), sql, params);// 执行SQL语句
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);// 关闭连接
        }
        return result;
    }
    
    public static void main(String[] args) throws SQLException {
        String sql = "insert into pms_brand(brand_id, name) values (?, ?)";
        Object[] params = { "1999", "Java" };
        int operate = operate(sql, params);// 向数据库中插入一条数据
        System.out.println(operate);

    }
}

说明

返回值	方法名	说明
int[]	batch(Connection conn, String sql, Object[][] params)	批量执行INSERT、UPDATE或DELETE
int[]	batch(String sql, Object[][] params)	批量执行INSERT、UPDATE或DELETE
T	insert(Connection conn, String sql, ResultSetHandler rsh)	执行一个插入查询语句
T	insert(Connection conn, String sql, ResultSetHandler rsh, Object… params)	执行一个插入查询语句
T	insert(String sql, ResultSetHandler rsh)	执行一个插入查询语句
T	insert(String sql, ResultSetHandler rsh, Object… params)	执行一个插入查询语句
T	insertBatch(Connection conn, String sql, ResultSetHandler rsh, Object[][] params)	批量执行插入语句
T	insertBatch(String sql, ResultSetHandler rsh, Object[][] params)	批量执行插入语句
T	query(Connection conn, String sql, ResultSetHandler rsh)	查询
T	query(Connection conn, String sql, ResultSetHandler rsh, Object… params)	查询
T	query(String sql, ResultSetHandler rsh)	查询
T	query(String sql, ResultSetHandler rsh, Object… params)	查询
int	update(Connection conn, String sql)	执行INSERT、UPDATE或DELETE
int	update(Connection conn, String sql, Object… params)	执行INSERT、UPDATE或DELETE
int	update(Connection conn, String sql, Object param)	执行INSERT、UPDATE或DELETE
int	update(String sql)	执行INSERT、UPDATE或DELETE
int	update(String sql, Object… params)	执行INSERT、UPDATE或DELETE
int	update(String sql, Object param)	执行INSERT、UPDATE或DELETE


@SpringBootTest
class Demo81MongodbTestApplicationTests {

        private Connection conn = null;

    private static String URL = "jdbc:mysql://216.127.*.*:3166/sgymall_pms?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false";
    private static String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String USERNAME = "root";
    private static String PASSWORD = "root";

        @BeforeEach
        public void initConnection() throws SQLException, ClassNotFoundException
        {
            printCurrentMethodName();
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USERNAME,PASSWORD);
        }

        public void initDatabase() throws SQLException
        {
            printCurrentMethodName();
            QueryRunner runner = new QueryRunner();
            runner.update(
                    conn,
                    "CREATE TABLE IF NOT EXISTS USER_INFO (userId VARCHAR(20) PRIMARY KEY, userName VARCHAR(50))");
        }

        @AfterEach
        public void destory()
        {
            printCurrentMethodName();
            DbUtils.closeQuietly(conn);
        }

        /**
         * 打印当前运行方法名称
         */
        public void printCurrentMethodName() {
            System.out.println(Thread.currentThread().getStackTrace()[2]
                    .getMethodName());
            System.out.println("==================================================");
        }

01.update


@Test
public void update() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   String suffix = Long.toHexString(System.currentTimeMillis());
   Integer result = runner.update(conn,
         "insert into USER_INFO(userId, userName) values(?, ?)", suffix,
         "name" + suffix);
   System.out.println("受影响记录条数：" + result);
}

02.查询

1.ScalarHandler

会返回一个对象，用于读取结果集中第一行指定列的数据。查询表中总记录数为例：

@Test
public void queryByScalarHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   Number number = runner.query(conn, "select count(*) from USER_INFO",
         new ScalarHandler<Number>());
   System.out.println("总记录记录条数：" + number.intValue());
}


initConnection
==================================================
queryByScalarHandler
==================================================
总记录记录条数：1
destory

2.ArrayHandler

会返回一个数组，用于将结果集第一行数据转换为数组。

  @Test
    public void queryByArrayHandler() throws SQLException
    {
        printCurrentMethodName();
        QueryRunner runner = new QueryRunner();
        Object[] results = runner.query(conn, "select * from USER_INFO",
                new ArrayHandler());
        System.out.println(Arrays.asList(results));
    }





initConnection
==================================================
queryByArrayHandler
==================================================
[184b4fe0a67, name184b4fe0a67]
destory
==================================================

3.ArrayListHandler

会返回一个集合，集合中的每一项对应结果集指定行中的数据转换后的数组。

  @Test
    public void queryByArrayListHandler() throws SQLException
    {
        printCurrentMethodName();
        QueryRunner runner = new QueryRunner();
        List<Object[]> results = runner.query(conn, "select * from USER_INFO",
                new ArrayListHandler());
        for (Object[] object : results)
        {
            System.out.println(Arrays.asList(object));
        }
    }
    
    
    
    
    initConnection
==================================================
queryByArrayListHandler
==================================================
[184b4fe0a67, name184b4fe0a67]
[184b5058ddd, name184b5058ddd]
destory
==================================================

4.KeyedHandler

会返回一个Map，我们可以指定某一列的值作为该Map的键，Map中的值为对应行数据转换的键值对，键为列名。

    @Test
    public void queryByKeyedHandler() throws SQLException
    {
        printCurrentMethodName();
        QueryRunner runner = new QueryRunner();
        Map<String, Map<String, Object>> results = runner.query(conn,
                "select * from USER_INFO", new KeyedHandler<String>("userName"));

        System.out.println(results);
    }


initConnection
==================================================
queryByKeyedHandler
==================================================
{name184b5058ddd={userId=184b5058ddd, userName=name184b5058ddd}, name184b4fe0a67={userId=184b4fe0a67, userName=name184b4fe0a67}}
destory
==================================================

5.ColumnListHandler

会返回一个集合，集合中的数据为结果集中指定列的数据。

   @Test
    public void queryByColumnListHandler() throws SQLException
    {
        printCurrentMethodName();
        QueryRunner runner = new QueryRunner();
        List<String> results = runner.query(conn, "select * from USER_INFO",
                new ColumnListHandler<String>("userName"));
        System.out.println(results);
    }




initConnection
==================================================
queryByColumnListHandler
==================================================
[name184b4fe0a67, name184b5058ddd]
destory
==================================================

6.MapHandler

会将结果集中第一行数据转换为键值对，键为列名。


@Test
public void queryByMapHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   Map<String, Object> results = runner.query(conn,
         "select * from USER_INFO", new MapHandler());
   System.out.println(results);
}



initConnection
==================================================
queryByMapHandler
==================================================
{userId=184b4fe0a67, userName=name184b4fe0a67}
destory
==================================================

7.MapListHandler

会将结果集中的数据转换为一个集合，集合中的数据为对应行转换的键值对，键为列名

@Test
public void queryByMapListHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   List<Map<String, Object>> results = runner.query(conn,
         "select * from USER_INFO", new MapListHandler());
   System.out.println(results);
}



initConnection
==================================================
queryByMapListHandler
==================================================
[{userId=184b4fe0a67, userName=name184b4fe0a67}, {userId=184b5058ddd, userName=name184b5058ddd}]
destory
==================================================

8.BeanHandler

实现了将结果集第一行数据转换为Bean对象，在实际应用中非常方便。

import java.text.MessageFormat;
 
public class UserInfo
{
   private String userId;
   private String userName;
 
   public String getUserId()
   {
      return userId;
   }
 
   public void setUserId(String userId)
   {
      this.userId = userId;
   }
 
   public String getUserName()
   {
      return userName;
   }
 
   public void setUserName(String userName)
   {
      this.userName = userName;
   }
 
   @Override
   public String toString()
   {
      return MessageFormat
            .format("[userId:{0},userName:{1}]", userId, userName);
   }
}


  @Test
    public void queryByBeanHandler() throws SQLException
    {
        printCurrentMethodName();
        QueryRunner runner = new QueryRunner();
        UserInfo results = runner.query(conn, "select * from USER_INFO",
                new BeanHandler<UserInfo>(UserInfo.class));
        System.out.println(results);
    }


initConnection
==================================================
queryByBeanHandler
==================================================
[userId:184b4fe0a67,userName:name184b4fe0a67]
destory
==================================================

9.BeanListHandler

只转换结果集的第一行，而BeanListHandler会将结果集的所有行进行转换，返回一个集合。

@Test
public void queryByBeanListHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   List<UserInfo> results = runner.query(conn, "select * from USER_INFO",
         new BeanListHandler<UserInfo>(UserInfo.class));
   System.out.println(results);
}



initConnection
==================================================
queryByBeanListHandler
==================================================
[[userId:184b4fe0a67,userName:name184b4fe0a67], [userId:184b5058ddd,userName:name184b5058ddd]]
destory
==================================================

10.BeanMapHandler

会将结果集转换为Bean对象，不过返回的是已指定列的值作为键的键值对。

    @Test
    public void queryByBeanMapHandler() throws SQLException
    {
        printCurrentMethodName();
        QueryRunner runner = new QueryRunner();
        Map<String, UserInfo> results = runner.query(conn,
                "select * from USER_INFO", new BeanMapHandler<String, UserInfo>(
                        UserInfo.class,"userName"));
        System.out.println(results);
    }
    
    
    initConnection
==================================================
queryByBeanMapHandler
==================================================
{name184b5058ddd=[userId:184b5058ddd,userName:name184b5058ddd], name184b4fe0a67=[userId:184b4fe0a67,userName:name184b4fe0a67]}
destory
==================================================