Java DbUtils实用

     <dependency>
            <groupId>commons-dbutilsgroupId>
            <artifactId>commons-dbutilsartifactId>
            <version>1.7version>
        dependency>

        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <version>8.0.30version>
        dependency>
1
2
3
4
5
6
7
8
9
10
11

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));
        }
    }
}
    
    
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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);

    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

• 说明

@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("==================================================");
        }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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);
}
1
2
3
4
5
6
7
8
9
10
11
12

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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
==================================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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
==================================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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
==================================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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
==================================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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
==================================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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
==================================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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);
   }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

  @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
==================================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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
==================================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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
==================================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19