纸绘武身（数据库连接池）

package JDBC1;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;

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

public class

Joint

{
public static void main(String[] args) throws SQLException {
//c3p0();
druid();
}

public static void

c3p0()

throws SQLException {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
Connection connection = dataSource.getConnection();
System.out.println(connection);
connection.close();//归还连接
}

public static void

druid()

{
PreparedStatement preparedStatement=null;
Connection connection =null;
try {
connection = Tools.getConnection();
String sql = “insert into demo values(?,?)”;
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,8);
preparedStatement.setInt(2,300);
int count = preparedStatement.executeUpdate();
System.out.println(count);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
Tools.close(preparedStatement,connection);
}
}//druid
}//end

package JDBC1;

import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

public class

JDBCTemplate

{

JdbcTemplate template=new JdbcTemplate(Tools.getDataSource());
//修改记录
@Test
public void

test0()

{
String sql = “update demo set num =111 where id=1”;
int update = template.update(sql);

}
@Test
//添加记录
public void test1(){
String sql = “insert into demo (id,num) values(?,?)”;
template.update(sql, 1, 66);

}
@Test
//删除记录
public void test2(){
String sql = “delete from demo where id=?”;
template.update(sql,1);
}
@Test
//查询一条记录并封装map
public void test3(){
String s = “select * from demo where id=?”;
Map<String, Object> map = template.queryForMap(s, 1);
System.out.println(map);
}
@Test
//查询所有记录封装为list
public void test4(){
String s = “select * from demo”;
List<Map<String, Object>> mapList = template.queryForList(s);
for (Map<String, Object> stringObjectMap : mapList) {
System.out.println(stringObjectMap);
}
}
@Test
//查询所有记录封装为emp的list集合
public void test5(){
String s = “select * from demo”;
List list = template.query(s, new BeanPropertyRowMapper<>(Emp.class));
for (Emp emp : list) {
System.out.println(emp);
}
}
@Test
//查询总记录
public void test6(){
String s = “select count(id) from demo”;
Long aLong = template.queryForObject(s, Long.class);
System.out.println(aLong);
}//test5
}//end

package JDBC1;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class

Tools

{
private static DataSource dataSource;
static {
try {
Properties properties = new Properties();
properties.load(Tools.class.getClassLoader().getResourceAsStream(“druid.properties”));
dataSource= DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}//static
/**

• 获取连接
  */
  public static Connection

getConnection()

throws SQLException {
return dataSource.getConnection();
}
/**

• 释放资源
  */
  public static void

close

(ResultSet resultSet, Statement statement, Connection connection){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}//close
public static void close(Statement statement, Connection connection){
close(null,statement,connection);
}
/**

• 获取连接池方法
  */
  public static DataSource

getDataSource()

{
return dataSource;
}
}//end
package JDBC1;

public class

Emp

{
private int id;
private int num;

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public int getNum() {
return num;
}

public void setNum(int num) {
this.num = num;
}

@Override
public String toString() {
return “Emp{” +
“id=” + id +
“, num=” + num +
‘}’;
}
}

c3p0-config.xml

<c3p0-config>

    <!-- c3p0默认配置，下面还可以配置多个数据库 -->
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db1?serverTimezone=GMT</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <!-- 初始化时获取连接数 -->
        <property name="initialPoolSize">6</property>
        <!-- 连接池中保留的最大连接数 -->
        <property name="maxPoolSize">50</property>
        <!-- 最大空闲时间，多少秒内未使用则连接被丢弃。若为0则永不丢弃。默认值: 0 -->
        <property name="maxIdleTime">1000</property>
    </default-config>

</c3p0-config>

druid.properties

diver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/db1?serverTimezone=GMT
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000