JDBC：连接数据库、CRUD操作、事务、连接池、BaseDao封装。

JDBC概述

什么是JDBC

JDBC是Java数据库连接，用Java语言来操作数据库的技术。

使用JBDC完成添加操作

JDBC使用步骤

  • 加载驱动
  • 创建数据库连接
  • 创建Statement并发送命令
  • 处理ResultSet返回结果集
  • 关闭数据库资源

代码

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
package com.leo.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo1 {

    static {
        try {
            // 加载JDBC驱动
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) throws SQLException {
        // 数据库连接信息
        String url = "jdbc:mysql://localhost:3306/leo?useUnicode=true&useSSL=false&characterEncoding=UTF8";
        String username = "root";
        String password = "123456";
        // 创建数据库连接
        Connection connection = DriverManager.getConnection(url, username, password);
        // 创建SQL命令执行平台
        Statement statement = connection.createStatement();
        String sql = "insert into student values(1, '张三', 23, '男', '曹县')";
        // 执行sql
        // 增删改都用这个方法
        int n = statement.executeUpdate(sql);
        if(n > 0) {
            System.out.println("添加成功");
        }else {
            System.out.println("添加失败");
        }
        // 关闭资源
        statement.close();
        connection.close();
    }
}

URL详解

jdbc:mysql://localhost:3306/leo?useUnicode=true&useSSL=false&characterEncoding=UTF8

jdbc：主协议

mysql：子协议

localhost：mysql服务器的地址

3306：端口号

leo：数据库名称

使用JDBC完成修改和删除

修改

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
package com.leo.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Demo2 {

    static String url = "jdbc:mysql://localhost:3306/leo?useUnicode=true&useSSL=false&characterEncoding=UTF8";
    static String username = "root";
    static String password = "123456";
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) throws Exception {
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();
        String sql = "update student set name='迪丽热巴' where id=1";
        statement.executeUpdate(sql);
        statement.close();
        connection.close();
    }
}

删除

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
48
49
package com.leo.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo3 {

    static String url = "jdbc:mysql://localhost:3306/leo?useUnicode=true&useSSL=false&characterEncoding=UTF8";
    static String username = "root";
    static String password = "123456";
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement= null;
        try {
            connection = DriverManager.getConnection(url, username, password);
            statement = connection.createStatement();
            String sql = "delete from student where id = 1";
            statement.executeUpdate(sql);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            if(statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

DBUtils封装

代码

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
package com.leo.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtils {

    private static final String driverClass = "com.mysql.jdbc.Driver";
    private static final String url = "jdbc:mysql://localhost:3306/leo?useUnicode=true&useSSL=false&characterEncoding=UTF8";
    private static final String username = "root";
    private static final String password = "123456";
    
    static {
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public static void close(AutoCloseable... closeables) {
        for (AutoCloseable closeable : closeables) {
            if(closeable != null) {
                try {
                    closeable.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
}

使用DBUtils完成添加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
package com.leo.jdbc;

import com.leo.utils.DBUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo4 {
    public static void main(String[] args) throws SQLException {
        Connection connection = DBUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "insert into student values(1, '张三', 23, '男', '曹县')";
        statement.executeUpdate(sql);
        DBUtils.close(statement, connection);
    }
}

JDBC完成查询

初始化数据

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
package com.leo.jdbc;

import com.leo.utils.DBUtils;

import java.sql.Connection;
import java.sql.Statement;
import java.util.Random;

public class Demo5 {
    public static void main(String[] args) throws Exception {
        Connection connection = DBUtils.getConnection();
        Statement statement = connection.createStatement();
        Random random = new Random();
        for (int i = 1; i <= 20; i++) {
            Integer id = i;
            String name = "小米" + i;
            Integer age= random.nextInt(100);
            String sex = random.nextBoolean() ? "男": "女";
            String address = "曹县" +i;
            String sql = "insert into student " +
                    "values ("+id+", '"+name+"', "+age+", '"+sex+"', '"+address+"')";
            statement.executeUpdate(sql);
        }
        DBUtils.close(statement, connection);
    }
}

查询

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
package com.leo.jdbc;

import com.leo.utils.DBUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class Demo6 {
    public static void main(String[] args) throws Exception {
        Connection connection = DBUtils.getConnection();
        Statement statement = connection.createStatement();
        // 执行查询语句
        String sql = "select * from student";
        // 获取结果集
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            // 根据索引获取字段，从1
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            int age = resultSet.getInt(3);
            String sex = resultSet.getString("sex");
            String address = resultSet.getString("address");
            System.out.println(id+","+name+","+age+","+sex+","+address);
        }
        DBUtils.close(resultSet, statement, connection);
    }
}

使用JDBC完成分页查询

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
package com.leo.jdbc;

import com.leo.utils.DBUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class Demo7 {
    public static void main(String[] args) throws Exception {
        Connection connection = DBUtils.getConnection();
        Statement statement = connection.createStatement();
        // 查询总条数
        String sql = "select count(*) from student";
        ResultSet countSet = statement.executeQuery(sql);
        countSet.next();
        int totalCount = countSet.getInt(1);
        int pageSize = 6;
        // 计算总页数
        int totalPage = (int) Math.ceil(totalCount * 1.0 / pageSize);
        for (int i = 1; i <= totalPage; i++) {
            int index = (i - 1) * pageSize;
            sql = "select * from student order by age desc limit "+index+", "+pageSize;
            ResultSet resultSet = statement.executeQuery(sql);
            System.out.println("第" + i + "页");
            while (resultSet.next()) {
                // 根据索引获取字段，从1
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                int age = resultSet.getInt(3);
                String sex = resultSet.getString("sex");
                String address = resultSet.getString("address");
                System.out.println(id+","+name+","+age+","+sex+","+address);
            }
            System.out.println();
        }



    }
}

SQL注入问题

演示问题

编写登录逻辑

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
package com.leo.jdbc;

import com.leo.utils.DBUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Demo8 {
    public static void main(String[] args) throws Exception {
        Connection connection = DBUtils.getConnection();
        Statement statement = connection.createStatement();
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = scanner.nextLine();
        System.out.println("请输入密码");
        String password = scanner.nextLine();
        String sql = "select * from sys_user where username='"+
                username+"' and password='"+password+"'";
        ResultSet resultSet = statement.executeQuery(sql);
        if(resultSet.next()) {
            int id = resultSet.getInt("id");
            String u = resultSet.getString("username");
            String p = resultSet.getString("password");
            System.out.println("登录成功：" + id + "," + u + "," + p);
        }else {
            System.out.println("登录失败");
        }
        DBUtils.close(resultSet, statement, connection);

    }
}

解决方案

解决方案非常简单，使用 PreparedStatement 类即可

该类实例包含已编译的SQL语句

改造代码

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
package com.leo.jdbc;

import com.leo.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Demo9 {
    public static void main(String[] args) throws Exception {
        Connection connection = DBUtils.getConnection();
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = scanner.nextLine();
        System.out.println("请输入密码");
        String password = scanner.nextLine();
        String sql = "select * from sys_user where username=? and password = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        // 使用setXxx传参
        statement.setString(1, username);
        statement.setString(2, password);
        ResultSet resultSet = statement.executeQuery();
        if(resultSet.next()) {
            int id = resultSet.getInt("id");
            String u = resultSet.getString("username");
            String p = resultSet.getString("password");
            System.out.println("登录成功：" + id + "," + u + "," + p);
        }else {
            System.out.println("登录失败");
        }
        DBUtils.close(resultSet, statement, connection);
    }
}

事务

使用事务完成转账操作

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
package com.leo.jdbc;

import com.leo.utils.DBUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Demo10 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DBUtils.getConnection();
            statement = connection.createStatement();
            // 开启事务
            connection.setAutoCommit(false);
            String sql1 = "update account set amount=amount+1000 where aid=1";
            String sql2 = "update account set amount=amount-1000 where aid=2";
            statement.executeUpdate(sql1);
            statement.executeUpdate(sql2);
            // 提交事务
            connection.commit();
        }catch (Exception e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            DBUtils.close(statement, connection);
        }
    }
}

JDBC批处理

只建立一次连接，统一的处理多条SQL语句

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
package com.leo.jdbc;

import com.leo.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class Demo11 {
    public static void main(String[] args) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DBUtils.getConnection();
            connection.setAutoCommit(false);
            String sql = "delete from student where id = ?";
            statement = connection.prepareStatement(sql);
            for (int i = 1; i < 5; i++) {
                statement.setInt(1, i);
                statement.addBatch();
            }
            statement.executeBatch();
            connection.commit();
        }catch (Exception e) {
            e.printStackTrace();
            connection.rollback();
        }finally {
            DBUtils.close(statement, connection);
        }
    }
}

连接池

什么是连接池

连接池是创建和管理一个JDBC连接的池化技术，这些连接会预先准备好放到池中，供任何需要它们的线程去使用。

为什么要使用连接池

连接对象销毁和创建是需要耗费时间的，在服务器初始化的时候就初始化一些连接，把这些连接放到内存中，需要使用的时候直接从内存中获取，使用完毕后再放入连接池中，提高效率。

手写JDBC连接池

创建配置文件

1
2
3
4
5
6
7
jdbcDriver = com.mysql.jdbc.Driver
jdbcUrl = jdbc:mysql://localhost:3306/leo?useUnicode=true&characterEncoding=UTF-8
username = root
password = 123456
initConnectCount = 20
maxConnects = 100
incrementCount = 3

数据库连接类

Connection类无法记录该连接是否空闲，此外，Connection类的close方法真的把连接给关掉了，这不是我们想要的。我们想要的是close时把连接放入池中。

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
package com.leo.pool;

import java.sql.Connection;

public class PoolConnection {
    
    private Connection connection = null;
    
    private boolean isUse = false;

    public PoolConnection(Connection connection, boolean isUse) {
        this.connection = connection;
        this.isUse = isUse;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public boolean isUse() {
        return isUse;
    }

    public void setUse(boolean use) {
        isUse = use;
    }
    
    public void close() {
        this.isUse = false;
    }
}

编写连接池接口

1
2
3
4
5
6
7
8
9
10
11
12
package com.leo.pool;

import java.sql.Connection;

public interface Pool {
    
    PoolConnection getConnection();
    
    Connection getConnectionNoPool();
    
}

编写实现类

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
package com.leo.pool;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;

public class JdbcPool implements Pool{

    private static String jdbcDriver;
    private static String jdbcUrl;
    private static  String username;
    private static String password;
    private static Integer initConnectCount;
    private static Integer maxConnects;
    private static Integer incrementCount;
    private static Vector<PoolConnection> connections = new Vector<>();

    {
        InputStream is = JdbcPool.class.getClassLoader()
                .getResourceAsStream("db.properties");
        Properties properties = new Properties();
        try {
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        jdbcDriver = properties.getProperty("jdbcDriver");
        jdbcUrl = properties.getProperty("jdbcUrl");
        username = properties.getProperty("username");
        password = properties.getProperty("password");
        initConnectCount = Integer.valueOf(properties.getProperty("initConnectCount"));
        maxConnects = Integer.valueOf(properties.getProperty("maxConnects"));
        incrementCount = Integer.valueOf(properties.getProperty("incrementCount"));

        try {
            Class.forName(jdbcDriver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        createConnections(initConnectCount);
    }

    private void createConnections(int count) {
        for (int i = 0; i < count; i++) {
            if(maxConnects > 0 && connections.size() >= maxConnects) {
                throw new RuntimeException("连接池中连接数已达到最大值");
            }
            try {
                Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
                connections.add(new PoolConnection(connection, false));
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }


    @Override
    public PoolConnection getConnection() {
        if(connections.isEmpty()) {
            throw new RuntimeException("连接池中没有连接");
        }
        return getActiveConnection();
    }

    private synchronized PoolConnection getActiveConnection() {
        while (true) {
            for (PoolConnection con : connections) {
                if(!con.isUse()) {
                    con.setUse(true);
                    return con;
                }
            }
            // 整个循环走完之后依然没有获取到连接
            if(connections.size() <= maxConnects - incrementCount) {
                createConnections(incrementCount);
            }else if(connections.size() < maxConnects && connections.size() >= maxConnects - incrementCount) {
                createConnections(maxConnects - connections.size());
            }
        }
    }

    @Override
    public Connection getConnectionNoPool() {
        try {
            return DriverManager.getConnection(jdbcUrl, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


}

创建一个管理器用来管理连接池

1
2
3
4
5
6
7
8
9
10
11
package com.leo.pool;

public class PoolManager {
    private static class CreatePool {
        private static JdbcPool pool = new JdbcPool();
    }
    public static JdbcPool getInstance() {
        return CreatePool.pool;
    }
}

案例测试

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
48
49
50
51
package com.leo.jdbc;

import com.leo.pool.JdbcPool;
import com.leo.pool.PoolConnection;
import com.leo.pool.PoolManager;
import com.leo.utils.DBUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.concurrent.CountDownLatch;

public class Demo12 {
    private static JdbcPool pool = PoolManager.getInstance();
    public static void main(String[] args) throws InterruptedException {
        long start = System.currentTimeMillis();
        CountDownLatch latch = new CountDownLatch(200);
        for (int i = 0; i < 200; i++) {
            new Thread(()->{
                try {
                    testPool();
                    System.out.println(Thread.currentThread().getName()+"执行完毕");
                } catch (Exception e) {
                    e.printStackTrace();
                }finally {
                    latch.countDown();
                }
            }).start();
        }
        latch.await();
        long end = System.currentTimeMillis();
        System.out.println(end - start);
    }

    public static void testPool() throws Exception {
        PoolConnection poolConnection = pool.getConnection();
        Connection connection = poolConnection.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from sys_user");
        DBUtils.close(resultSet, statement);
        poolConnection.close();
    }

    public static void testNoPool() throws Exception {
        Connection connection = pool.getConnectionNoPool();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from sys_user");
        DBUtils.close(resultSet, statement, connection);
    }

}

BaseDao封装

概述

为了能使我们开发的过程中只需要关注SQL语句，而不需要关注JDBC的使用流程，需要封装出一套更加通用的工具——BaseDao

代码

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
package com.leo.utils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

public class BaseDao {

    public <T> List<T> selectList(String sql, Class<T> clazz, Object... params) {
        List<T> list =new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                Object p = params[i];
            }
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i + 1);
                    Object value = resultSet.getObject(columnName);
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(resultSet, statement, connection);
        }
        return list;
    }
    
    public <T> T selectOne(String sql, Class<T> clazz, Object... params) {
        List<T> list = selectList(sql, clazz, params);
        if(list != null && list.size() >= 1) {
            return list.get(0);
        }
        return null;
    }

    public boolean update(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                Object p = params[i];
                statement.setObject(i+1, p);
            }
            int n = statement.executeUpdate();
            return n >= 1;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(statement, connection);
        }
        return false;
    }

}

分页封装

分页类Page

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
package com.leo.utils;

import java.util.List;

public class Page<T> {
    private List<T> data;
    private int totalPage;
    private int totalCount;

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }
}

BaseDao

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
package com.leo.utils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseDao {

    public <T> Page<T> selectPage(String sql, Class<T> clazz, int p, int size, Object... params) {
        Page<T> page = new Page<>();
        List<T> list =new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        Statement countStatement = null;
        ResultSet countRs = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            // 查询总条数，计算总页数
            countStatement = connection.createStatement();
            countRs = countStatement.executeQuery("select count(*) from (" + sql + ") as t");
            countRs.next();
            int totalCount = countRs.getInt(1);
            int totalPage = (int) Math.ceil(totalCount * 1.0 / size);
            page.setTotalPage(totalPage);
            page.setTotalCount(totalCount);
            sql += " limit "+p+"," + size;
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                Object param = params[i];
                statement.setObject(i+1, param);
            }
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i + 1);
                    Object value = resultSet.getObject(columnName);
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(resultSet, statement, connection);
        }
        page.setData(list);
        return page;
    }

    public <T> List<T> selectList(String sql, Class<T> clazz, Object... params) {
        List<T> list =new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                Object p = params[i];
                statement.setObject(i+1, p);
            }
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i + 1);
                    Object value = resultSet.getObject(columnName);
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(resultSet, statement, connection);
        }
        return list;
    }

    public <T> T selectOne(String sql, Class<T> clazz, Object... params) {
        List<T> list = selectList(sql, clazz, params);
        if(list != null && list.size() >= 1) {
            return list.get(0);
        }
        return null;
    }

    public boolean update(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                Object p = params[i];
                statement.setObject(i+1, p);
            }
            int n = statement.executeUpdate();
            return n >= 1;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(statement, connection);
        }
        return false;
    }

}

许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。