使用数据库连接池（DataSource）连接数据库、简化sql语句操作

使用数据库连接池（DataSource）连接数据库

1.c3p0连接池

打开idea --->在项目的根目录的平级建立lib文件夹--->将架包复制到lib文件夹--->并添加到库

在跟目录下新建c3p0配置文件，并写入以下格式

建立工具类

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.*;

public class C3P0Utils {
    private  static ComboPooledDataSource  comboPooledDataSource;
   static{
       comboPooledDataSource = new ComboPooledDataSource();
   }
    public static Connection getConnection()throws SQLException {

        return comboPooledDataSource.getConnection();
    }
    public static void close(Connection  con, PreparedStatement pst){

        if (con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (pst!=null){
            try {
                pst.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    public static void close(Connection  con, PreparedStatement pst, ResultSet rst) {
        close(con,pst);
        if (rst!=null){
            try {
                rst.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

}

2.Druid（德鲁伊连接池）

打开idea --->在项目的根目录的平级建立lib文件夹--->将架包复制到lib文件夹--->并添加到库

在跟目录下新建druid.properties配置文件，并写入以下格式

建立工具类

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

public class DruidUtils {
    private static DataSource dataSource;

    static {
        Properties properties = new Properties();
        try {
            properties.load(DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public DruidUtils() {}

    public static Connection getConnection()throws SQLException {

        return dataSource.getConnection();
    }

    public static void close(Connection  con, PreparedStatement pst){

        if (con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (pst!=null){
            try {
                pst.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    public static void close(Connection  con, PreparedStatement pst, ResultSet rst) {
        close(con,pst);
        if (rst!=null){
            try {
                rst.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

简化sql操作

1.DBUtils

导包

关键类

QueryRunner 执行sql语句的

DbUtils 关流

ResultSetHandler封装结果集的

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import java.util.List;

public class DBUtils {

    @Test
    public void insert() throws Exception{
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Object o[]={"111","111"};
        int update = qr.update("insert into test1 values (null,?,?)", o);
    }
    @Test
    public void query()throws Exception{
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        List<User> query = qr.query("select * from test1", new BeanListHandler<User>(User.class));
        System.out.println(query);
    }

}

2.SpringJdbcTemplate

导包

import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import utils.DruidUtils;
import utils.User;
import java.util.List;


public class Demo {

    @Test
    public void init(){
        JdbcTemplate template = new JdbcTemplate(DruidUtils.getDataSource());
        System.out.println(template);
    }
    @Test
    public void insert(){
        JdbcTemplate template = new JdbcTemplate(DruidUtils.getDataSource());
        Object o[]={"222","222"};
        int update = template.update("insert into test1 values (null,?,?)", o);
    }
    @Test
    public void query()throws Exception{
        JdbcTemplate template = new JdbcTemplate(DruidUtils.getDataSource());
        List<User> list = template.query
                ("select * from test1",
                        new BeanPropertyRowMapper<User>(User.class));
        System.out.println(list);

    }
}