【JDBC-Druid-JdbcTemplate】使用JdbcTemplate（通过Druid连接池）的方式操作数据库

﹏ヽ暗。殇╰゛Y 2023-10-08 21:55 37阅读 0赞

#### 【JDBC-Druid-JdbcTemplate】使用JdbcTemplate（通过Druid连接池）的方式操作数据库 ####

*  1）导入依赖
 *  2）定义JdbcUtils工具类
 *   *  2.1.定义工具类获取DruidDataSource
     *  2.2.配置druid.properties
 *  3）通过定义好的JdbcUtils工具类使用JdbcTemplate（Druid连接池）

## 1）导入依赖 ##

<dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-beans</artifactId>
        <version>5.0.0.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>5.0.0.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.0.0.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.0.0.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>5.0.0.RELEASE</version>
    </dependency>
    
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.6</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.23</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        
    </dependency>

## 2）定义JdbcUtils工具类 ##

### 2.1.定义工具类获取DruidDataSource ###

import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.pool.DruidDataSourceFactory;
    import org.springframework.jdbc.core.JdbcTemplate;
    
    import javax.sql.DataSource;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.Properties;
    
    public class JdbcUtils {
        
        private static DruidDataSource dataSource;
    	//获取DruidDataSource 
        public static DruidDataSource getDataSource() {
        
            dataSource = new DruidDataSource();
            dataSource.setInitialSize(1);
            dataSource.setMaxActive(2);
            dataSource.setMaxWait(3000);
            return dataSource;
        }
    	
    	//通过getDataSource()获取到的dataSource来创建JDBC连接信息
    	//传参的方式获取连接
        public static JdbcTemplate sourceJdbcTemplate(String url, String username, String password) {
        
            DruidDataSource dataSource = getDataSource();
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);
            return new JdbcTemplate(dataSource);
        }
    
    	//通过getDataSource()获取到的dataSource来创建JDBC连接信息
    	//写死的方式获取连接
        public static JdbcTemplate mysqlJdbcTemplate() {
        
            DruidDataSource dataSource = getDataSource();
            dataSource.setUrl(Config.mysqlUrl);
            dataSource.setUsername(Config.mysqlUsername);
            dataSource.setPassword(Config.mysqlPassword);
            return new JdbcTemplate(dataSource);
        }
    
    	//通过properties配置文件的方式获取连接
        public static DataSource getDataSourceFactory() {
        
            Properties properties = new Properties();
            try {
        
            properties.load(JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
                try {
        
                    dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
                } catch (Exception e) {
        
                    e.printStackTrace();
                }
            } catch (IOException e) {
        
                e.printStackTrace();
            }
            return dataSource;
        }
    	
    	//通过properties配置文件的方式获取连接，需要释放资源
        public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        
            if (conn != null) {
        
                try {
        
                    conn.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if (ps != null) {
        
                try {
        
                    ps.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if (rs != null) {
        
                try {
        
                    rs.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
    }

### 2.2.配置druid.properties ###

注意：

如果平时只需要连接一个数据库，不需要对数据库进行频繁切换的情况下，那么可以通过properties配置文件的方式获取数据库连接，方法为上面的getDataSourceFactory()，但是需要配置druid.properties。

`druid.properties`：

driverClassName=com.mysql.jdbc.Driver
    url=jdbc:mysql://192.168.1.1:3306/test
    username=root
    password=123456
    initialSize=1
    maxActive=2
    maxWait=3000

## 3）通过定义好的JdbcUtils工具类使用JdbcTemplate（Druid连接池） ##

使用Junit进行测试（下面只是样例，无法正常执行，仅供参考）:

public class Demo {
        
        private static DataSource dataSource;
        private static JdbcTemplate jdbcTemplate;
        private static Connection conn;
        private static PreparedStatement ps;
        private static ResultSet rs;
    
        @Test
        public void test1() {
        
            DataSource dataSourceFactory = JdbcUtils.getDataSourceFactory();
            try {
        
                conn = dataSourceFactory.getConnection();
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                while (rs.next()) {
        
                    int a = rs.getInt(1);
                    int b = rs.getInt(2);
                    int c = rs.getInt(3);
                }
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
                JdbcUtils.close(conn, ps, rs);
            }
        }
        
    	@Test
    	//JdbcTemplate.queryForList(sql，返回类型.class)直接返回一个List集合
    	public void test2(String httpUrl, String username, String password, String sourceDatabase, String likeTable) {
        
            String sql = null;
            jdbcTemplate = JdbcUtils.sourceJdbcTemplate(httpUrl, username, password);
            if (likeTable == null || "".equals(likeTable)) {
        
                sql = "SELECT lower(table_name) FROM information_schema.tables where table_schema = '" + sourceDatabase + "' and table_type != 'VIEW';";
            } else {
        
                sql = "SELECT lower(table_name) FROM information_schema.tables where table_schema = '" + sourceDatabase + "' and table_name like '" + likeTable + "' and table_type != 'VIEW';";
            }
            return jdbcTemplate.queryForList(sql, String.class);
        }
    
    	@Test
    	//JdbcTemplate.query(sql，new RowMapper(返回的数据类型，可以为bean类型))直接返回一个List集合
    	//如果循环执行JdbcTemplate.query()，那么每次循环都会返回一个List1，需要外部定义一个List2，使用List2.addAll(List1)的方式对每次循环产生的List1进行收集。
        public void test3(String httpUrl, String username, String password, String sourceDatabase, List<String> mysqlLackTables, int dataSourceInfoId, String sourceName) {
        
            List<TableComment> tableComments = new ArrayList<>();
            jdbcTemplate = JdbcUtils.sourceJdbcTemplate(httpUrl, username, password);
            String sql = "SELECT lower(TABLE_NAME), TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + sourceDatabase + "' and lower(TABLE_NAME) = lower('%s')";
            for (String mysqlLackTable : mysqlLackTables) {
        
                tableComments.addAll(jdbcTemplate.query(String.format(sql, mysqlLackTable), new RowMapper<TableComment>() {
        
                    @Override
                    public TableComment mapRow(ResultSet rs, int i) throws SQLException {
        
                        TableComment tableComment = new TableComment();
                        tableComment.setTableName(rs.getString(1));
                        tableComment.setTableComment(rs.getString(2));
                        return tableComment;
                    }
                }));
            }
        }
        
    	@Test
    	//JdbcTemplate.update(sql)对数据进行更新，写入，删除操作
    	public void test4(List<TableComment> list, int dataSourceInfoId, String sourceName) {
        
            jdbcTemplate = JdbcUtils.mysqlJdbcTemplate();
            String sql = "insert into `data_resource_table_info` (s_table_name,s_table_name_zh,d_table_name,d_table_name_zh,approve_sts,data_source_id,data_source_name,sts,sts_date,create_user_id,create_time,update_user_id,update_time,remarks) values('%s','%s','%s','%s','C'," + dataSourceInfoId + ",'" + sourceName + "','A',NOW(),1,NOW(),1,NOW(),null)";
            list.stream().forEach(tableComment -> {
        
                String tableName = tableComment.getTableName();
                String comment = tableComment.getTableComment();
                jdbcTemplate.update(String.format(sql, tableName, comment, tableName, comment));
            });
        }
    }