JDBC,数据库连接池,DBUtils

JDBCUtils:

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtil {
    private static DataSource dataSource; //数据库连接池
    static {
        Properties properties = new Properties();
        InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(is);
            is.close();
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        }  catch (IOException ioException) {
            ioException.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws Exception {
        return dataSource.getConnection();
    }
    public static Connection getConnectionOld() throws IOException, ClassNotFoundException, SQLException {
        InputStream is=JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        is.close();

        String driverClass=properties.getProperty("driverClass");
        String url=properties.getProperty("url");
        String user=properties.getProperty("user");
        String password=properties.getProperty("password");

        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;

    }

    public static void close(Connection connection){
       close(connection,null,null);
    }

    public static void close(Connection connection, Statement statement){
        close(connection,statement,null);
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        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();
            }
        }
    }

}

druid.properties:

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc?characterEncoding=UTF-8
username=root
password=123456

#初始化容量
initialSize = 3
#最大激活数
maxActive =100
#最小发呆数
minIdle =10

CommonUtil:

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CommonUtil {
    public static int update(Connection connection,String sql,Object...args) throws SQLException, IOException, ClassNotFoundException {

        PreparedStatement preparedStatement = null;
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            preparedStatement.setObject(i+1,args[i]);
        }
        return preparedStatement.executeUpdate();



    }
    public static void view(Connection connection,String sql,Object...args) throws SQLException, IOException, ClassNotFoundException {
        List<Customer> list = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        preparedStatement = connection.prepareStatement(sql);
        //设置参数
        for (int i = 0; i < args.length; i++) {
            preparedStatement.setObject(i+1,args[i]);
        }

        resultSet = preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData =resultSet.getMetaData();

        //输出表头
        for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
            System.out.print(resultSetMetaData.getColumnLabel(i+1)+"\t");
        }
        System.out.println();


        while (resultSet.next()) {
            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                Object object = resultSet.getObject(resultSetMetaData.getColumnLabel(i + 1));
                System.out.print(object+"\t");
            }
            System.out.println();
        }

    }
}

 

JDBCDAO:

import com.atguigu.day23.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class JdbcDao<T> {
    private QueryRunner queryRunner = new QueryRunner();
    private Class<T> clazz;

    public JdbcDao(Class<T> clazz) {
        this.clazz = clazz;
    }
    public T getBean(String sql, Object... args) throws Exception {
        Connection connection = null;
        try {
            connection = JdbcUtil.getConnection();
            BeanHandler<T> beanHandler = new BeanHandler<T>(clazz);
            return queryRunner.query(connection, sql, beanHandler, args);
        } finally {
            JdbcUtil.close(connection);
        }
    }

    public List<T> getList(String sql, Object... args) throws Exception {
        Connection connection = null;
        try {
            connection = JdbcUtil.getConnection();
            BeanListHandler<T> beanListHandler = new BeanListHandler<T>(clazz);
            return queryRunner.query(connection, sql, beanListHandler, args);
        } finally {
            JdbcUtil.close(connection);
        }
    }

    public Object getValue(String sql, Object... args) throws Exception {
        Connection connection = null;
        try {
            connection = JdbcUtil.getConnection();
            ScalarHandler scalarHandler = new ScalarHandler();
            return queryRunner.query(connection, sql, scalarHandler, args);
        } finally {
            JdbcUtil.close(connection);
        }
    }

    public int update(String sql, Object... args) throws Exception {
        Connection connection = null;
        try {
            connection = JdbcUtil.getConnection();
            return queryRunner.update(connection, sql, args);
        } finally {
            JdbcUtil.close(connection);
        }
    }
}

 

查询测试:

 @Test
    public void test4(){
        JdbcDao<Customer> customerJdbcDao = new JdbcDao<>(Customer.class);
        try {
            Customer bean = customerJdbcDao.getBean("select * from customer where id =?",1);
            System.out.println("bean = " + bean);

            List<Customer> list = customerJdbcDao.getList("select * from customer where id<?", 4);
            System.out.println("list = " + list);

            Object value = customerJdbcDao.getValue("select avg(age) from customer");
            System.out.println("value = " + value);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

 

JDBC,数据库连接池,DBUtils

上一篇:linux 安装 Logtash 同步mysql数据到Elasticsearch


下一篇:Java 回顾(1)