用 Druid 连接数据库

1. 实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TestTable {
    private Integer id;
    private String name;
    private Integer age;
}

数据库表：

2. Maven依赖

        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.6</version>
        </dependency>
        <!--druid连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--jdbc依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

3. 配置类与配置文件

以下为整个 module 的结构：（实体类我放到了另外一个module）

db.properties:

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test_jan?rewriteBatchedStatements=true
username=root
password=1234
maxActive=8

JdbcUtil：

package com.janet.springcloud.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtil {
	public static DataSource ds = null;
	static {
		try {
			//1.加载配置文件
			Properties p = new Properties();
			//获取字节码目录
			String path = JdbcUtil.class.getClassLoader().getResource("db.properties").getPath();
			FileInputStream in = new FileInputStream(path);
			p.load(in);
			ds = DruidDataSourceFactory.createDataSource(p);
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	//获取数据源
	public static DataSource  getDataSource() {
		return ds;
	}
	public static Connection getConn() {
		try {
			// 2.连接数据
			return ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 关闭资源 
	 */
	public static void close(Connection conn,Statement st,ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}

4. 业务类

Dao：

package com.janet.springcloud.dao;

import com.janet.springcloud.entities.TestTable;
import com.janet.springcloud.util.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.springframework.stereotype.Component;
import java.sql.SQLException;

@Component
public class MySqlTestDao {
    private QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());

    public TestTable getTestTableById(Integer id) throws SQLException {
        String sql = "select * from testTable where id = ?";
        TestTable testTable = null;
        testTable = qr.query(sql, new BeanHandler<TestTable>(TestTable.class),id);
        return testTable;
    }

}

service：

@Service
public class PaymentServiceImpl implements PaymentService {
    @Autowired
    private MySqlTestDao mySqlTestDao;
    
    @Override
    public TestTable getTestTableById(Integer id) {
        try {
            TestTable testTableById = mySqlTestDao.getTestTableById(id);
            return testTableById;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


}

Controller：

@RestController
public class PaymentController {
    @Autowired
    private PaymentService paymentService;

    @GetMapping("/getTestTableById/{id}")
    public TestTable getTestTableById(@PathVariable("id") Integer id){
        return paymentService.getTestTableById(id);
    }
}

测试：