什么是连接池
连接池技术可以管理数据库连接，可以重复地使用数据库连接。关闭连接不代表销毁connection，只是把连接还给池子。
如何使用连接池
java提供了公共的接口 javax.sql.DataSource
常见的连接池有DBCP, C3P0, DRUID.
DBCP
Tomcat的内置连接池
使用时需要导入jar包
写个DBCPUtils工具类练手
其实唯一区别就是获取connection从DataSource而非DriverManager获取。
public class DBCPUtils {
// 定义常量
public static final String DRIVERNAME = "com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8";
public static final String USER = "root";
public static final String PASSWORD = "980909";
//创建连接池对象
public static BasicDataSource dataSource = new BasicDataSource();
//设置
static {
dataSource.setDriverClassName(DRIVERNAME);
dataSource.setUrl(URL);
dataSource.setUsername(USER);
dataSource.setPassword(PASSWORD);
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//释放
public static void close(Connection connection, Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
close(connection, statement);
}
}
其他常见配置项
C3P0
Spring Hibernate默认的连接池
使用需要以下资源
xml文件可以放在src下或者单独建一个资源文件夹，注意不要更改。
c3p0-config.xml
<c3p0-config>
<!--默认配置-->
<default-config>
<!-- initialPoolSize：初始化时获取三个连接，
取值应在minPoolSize与maxPoolSize之间。 -->
<property name="initialPoolSize">3</property>
<!-- maxIdleTime：最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。-->
<property name="maxIdleTime">60</property>
<!-- maxPoolSize：连接池中保留的最大连接数 -->
<property name="maxPoolSize">100</property>
<!-- minPoolSize: 连接池中保留的最小连接数 -->
<property name="minPoolSize">10</property>
</default-config>
<!--配置连接池mysql-->
<named-config name="mysql">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8</property>
<property name="user">root</property>
<property name="password">980909</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
</named-config>
<!--配置连接池2,可以配置多个-->
</c3p0-config>写个C3P0连接池练练手叭
public class C3P0Utils {
//因为有config文件，所以不需要自己定义常量了
//空参构造使用的就是配置文件中的配置
// public static ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//使用指定的配置：只需要给出配置文件名
public static ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("mysql");
public static Connection getConnection() throws SQLException {
return comboPooledDataSource.getConnection();
}
//释放
public static void close(Connection connection, Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
close(connection, statement);
}
}
常见配置项
Druid连接池 阿里yyds
使用所需资源
还是老样子，资源文件放在资源目录
druid.properties
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/db5?characterEncoding=UTF-8&rewriteBatchedStatements=true username=root password=980909 initialSize=5 maxActive=10 maxWait=3000
还是写个util工具类练练手
public class DruidUtils {
public static DataSource dataSource;
static {
try {
Properties properties = new Properties();
//德鲁伊不能主动加载配置文件
InputStream resourceAsStream = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(resourceAsStream);
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
public static DataSource getDataSource() {
return dataSource;
}
public static void close(Connection connection, Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
close(connection, statement);
}
}DbUtils
阿帕奇觉得我们的util太垃圾...
这个jar包，懂吧？
使用DbUtils进行增加 删除 更改操作
public class DBUtilsDemo02 {
@Test
public void testInsert() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
String sql = "insert into employee values(?, ?, ?, ?, ?, ?)";
Object[] params = {null, "张三", 20, "女", 10000, "1900-01-01"};
Connection connection = DruidUtils.getConnection();
int update = queryRunner.update(connection, sql, params);
DbUtils.closeQuietly(connection);
}
@Test
public void testUpdate() throws SQLException {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql = "update employee set salary = ? where ename = ? ";
Object[] params = {15000.0, "张三"};
//自动模式不需要传入connection对象
queryRunner.update(sql, params);
}
@Test
public void testDelete() throws SQLException {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql = "delete from employee where eid = ?";
queryRunner.update(sql, 1);
}
}用它进行查询操作
public class DbUtilsDemo03 {
@Test
public void testFindById() throws SQLException {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql = "select * from employee where eid = ?";
Object[] query = queryRunner.query(sql, new ArrayHandler(), 8);
System.out.println(Arrays.toString(query));
}
@Test
public void testFindAll() throws SQLException {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql = "select * from employee";
List<Object[]> query = queryRunner.query(sql, new ArrayListHandler());
for (Object[] arr : query) {
System.out.println(Arrays.toString(arr));
}
}
@Test
public void testFindBean() throws SQLException {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql = "select * from employee where eid = ?";
Employee e = queryRunner.query(sql, new BeanHandler<Employee>(Employee.class), 8);
System.out.println(e);
}
@Test
public void testFindAllBean() throws SQLException {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql = "select * from employee where salary > 3000";
List<Employee> query = queryRunner.query(sql, new BeanListHandler<Employee>(Employee.class));
for (Employee e : query) {
System.out.println(e);
}
}
//map handler 列名做key， 内容做val
@Test
public void testFindMap() throws SQLException {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql = "select * from employee where ename = ?";
Map<String, Object> map = queryRunner.query(sql, new MapHandler(), "张三");
for (Map.Entry<String, Object> entry : map.entrySet()) {
System.out.println(entry.getKey() + " " + entry.getValue());
}
}
@Test
public void testFindScalar() throws SQLException {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql = "select sum(salary) from employee";
Double query = queryRunner.query(sql, new ScalarHandler<Double>());
System.out.println(query);
}
}批处理
//批量插入
public class BatchInsert {
public static void main(String[] args) throws SQLException {
Connection connection = DruidUtils.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("insert into testBatch(uname) values (?)");
for (int i=0; i<10000; i++) {
preparedStatement.setString(1,"test" + i);
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
DruidUtils.close(connection, preparedStatement);
}
}Mysql元数据
什么是元数据
查询结果信息： UPDATE 或 DELETE语句 受影响的记录数。
数据库和数据表的信息： 包含了数据库及数据表的结构信息。
MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。
使用JDBC获取元数据
大体分两类， 数据库的元信息和结果集的元信息
例子1 获取数据库元信息
public void testDatabaseMetaData() throws SQLException {
Connection connection = DruidUtils.getConnection();
DatabaseMetaData metaData = connection.getMetaData();
System.out.println(metaData.getURL());
System.out.println(metaData.getUserName());
System.out.println(metaData.getDatabaseProductName());
System.out.println(metaData.getDatabaseProductVersion());
System.out.println(metaData.getDriverName());
System.out.println(metaData.isReadOnly());
connection.close();
}例子2 获取结果集的元数据信息
@Test
public void resultSetMetadata() throws SQLException {
Connection connection = DruidUtils.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select * from employee");
ResultSet resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
for (int i=1; i<metaData.getColumnCount();i++) {
System.out.println(metaData.getColumnName(i) + " " + metaData.getColumnTypeName(i));
}
DruidUtils.close(connection, preparedStatement);
}
京公网安备 11010502036488号