Database connection pool for java learning

0x00 Preface

The JDBC used earlier needs to re-establish objects every time it connects to the database. We will create a connection pool and return it to the connection pool after each use.

0x01 connection pool overview

A connection pool is actually a container (Collection) that holds database connections. After the system is initialized, the container is created, and some connection objects will be applied in the container. When the user accesses the database, the connection objects will be obtained from the container. After the user accesses, the connection objects will be returned to the container.

Using connection pool can save resources and enable users to access efficiently.

0x02 connection pool implementation

C3p0 create thread pool

Add jar files to the LIBS directory. The required files are:

c3p0-0.9.5.2.jar  c3p0包 mchange-commons-java-0.2.12.jar//c3p0 依赖包
mysql-connector //数据库连接驱动包

Set c3p0 config XML is placed in the SRC directory and configured.

Create connection pool code:

public class test1 {
    public static void main(String[] args) throws sqlException {
        DataSource dataSource = new ComboPooledDataSource();
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        


    }
}

Druid database connection pool

Import jar package

druid-1.0.9.jar

Import Druid Properties file

code:

public class test2 {
    public static void main(String[] args) throws Exception {
        Properties pro = new Properties();  //创建properties对象
        InputStream is = test2.class.getClassLoader().getResourceAsStream("druid.properties");   //获取配置文件资源
        pro.load(is);  //加载配置文件
        DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);   //创建连接池对象，并传入配置文件信息
        Connection connection = dataSource.getConnection();    //获取连接对象
        System.out.println(connection);



    }
}

After using these methods, you can find that it is more convenient to connect to the database. They are all encapsulated code.

Here we can define another tool class, encapsulate the connected things in the tool class, and simplify our code.

Tools:

public class JDBCutiss {
    private static DataSource ds;

    static {
        Properties pro = new Properties();
        InputStream rs = JDBCutiss.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            pro.load(rs);
            try {
                ds = DruidDataSourceFactory.createDataSource(pro);

            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }


        }
            //返回连接对象
        public static Connection getConnection() throws sqlException {
        return ds.getConnection();
        }
        public  static void close(Statement stmt,Connection conn){
        if (stmt!=null){
            try {
                stmt.close();
            } catch (sqlException throwables) {
                throwables.printStackTrace();
            }

        }
        if (conn!=null){
            try {
                conn.close();
            } catch (sqlException throwables) {
                throwables.printStackTrace();
            }
        }

        }
        //该方法返回定义好的DataSource对象
        public static DataSource getDataSource(){
        return ds;
        }


    }

Main method:

public class test2 {
    public static void main(String[] args) throws Exception {
        Connection connection = JDBCutiss.getConnection();//获取连接对象
        String sql = "select * from users where id =?";  //设置sql语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);//传入sql语句并创建预编译执行对象
        preparedStatement.setString(1,"1");  //sql语句设置值
//        System.out.println(sql);
        ResultSet resultSet = preparedStatement.executeQuery();  //执行sql语句
        while (resultSet.next()){
            System.out.println(resultSet.getString("id"));  //获取id
            System.out.println(resultSet.getString("username"));//获取用户账户
            System.out.println(resultSet.getString("password"));//获取密码
        }
        JDBCutiss.close(preparedStatement,connection);


    }
}

Spring JDBC

Spring framework's simple encapsulation of JDBC. Provides a JDBC template object to simplify JDBC development.

Create object:

JdbcTemplate template = new JdbcTemplate(ds);

Common methods:

* update():执行DML语句。增、删、改语句
		* queryForMap():查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合
			* 注意：这个方法查询的结果集长度只能是1
		* queryForList():查询结果将结果集封装为list集合
			* 注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
		* query():查询结果，将结果封装为JavaBean对象
			* query的参数：RowMapper
				* 一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
				* new BeanPropertyRowMapper<类型>(类型.class)
		* queryForObject：查询结果，将结果封装为对象
			* 一般用于聚合函数的查询

Template connection code:

public class test3 {
    public static void main(String[] args) {
        JdbcTemplate template = new JdbcTemplate(JDBCutiss.getDataSource());//从工具类获取对象传入
        String sql = "update users set password =? where id =?";


        int admin = template.update(sql,"500",2);  //参数sql语句需要的参数，再进行执行sql语句
        System.out.println(admin);



    }
}

Generally speaking, only three lines of code can realize such a database operation, which is much simpler than the previous methods. Other required functions are encapsulated in the tool class and can be called directly.

0x03 end

After using spring JDBC, it is found that the code is much easier than the previous ones. When developing programs with defined tool classes, you can directly copy them for use, or make a slight improvement before using them. Generally speaking, spring is still very convenient.