JDBC技术总结

一、导包

 

二、配置文件 

1. jdbc.properties

driver=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://127.0.0.1:3306/test
user=root
password=root

2.dbcp.properties

username=root
password=root
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test

initialSize=10
maxTotal=50
minIdle=5
maxWaitMillis=5000

3.c3p0-config.xml

<c3p0-config>
    <!-- This app is massive! -->
    <named-config name="c3p0">
        <!--指定连接数据源的基本属性-->
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test</property>

        <!--若数据库中列结束不足时，一次向数据库服务器申请多少个连接-->
        <property name="acquireIncrement">5</property>

        <!--初始化数据库连接池时连接的数量-->
        <property name="initialPoolSize">5</property>
        <!--数据库连接池中最小的数据库连接数-->
        <property name="minPoolSize">5</property>
        <!--数据库连接池中最大的数据库连接数-->
        <property name="maxPoolSize">10</property>

        <!-- C3P0 数据库可以维护的 Statement 的个数-->
        <property name="maxStatements">20</property>
        <!--每个链接可以同时使用 Statement 对象的个数-->
        <property name="maxStatementsPerConnection">5</property>

    </named-config>
</c3p0-config>

 

三、JBDC工具类

package com.jdbc.tools;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.beanutils.BeanUtils;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;

/**
 * JDBC 的工具类，其中封装了一些工具方法
 *
 * @author Administrator
 */

public class JDBCTools {

    //（开始事务）关闭事务自动提交
    public static void beginTX(Connection connection) {
        if (connection != null) {
            try {
                connection.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //提交事务
    public static void commit(Connection connection) {
        if (connection != null) {
            try {
                connection.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //回滚事务
    public static void rollback(Connection connection) {
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static DataSource dataSource = null;

    static {
        dataSource = new ComboPooledDataSource("c3p0");
    }

    /**
     * 1.获取连接的方法
     * 通过读取配置文件从数据库服务器获取一个连接
     *
     * @return
     * @throws Exception
     */

    public static Connection getConnection() throws Exception {
        return dataSource.getConnection();
    }

    /**
     * 2.关闭 Statement 、ResultSet 和  Connection 连接
     *
     * @param statement
     * @param connection
     */
    public static void release(ResultSet rs, Statement statement, Connection connection) {

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        if (statement != null) {
            try {
                statement.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }
        if (connection != null) {

            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
    }

    /**
     * 3.通用更新方法
     *
     * @throws SQLException
     */
    public static void update(String sql) {

        //1.获取数据库连接
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JDBCTools.getConnection();
            statement = connection.createStatement();
            statement.executeUpdate(sql);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JDBCTools.release(null, statement, connection);
        }

    }

    /**
     * 通用更新方法升级
     */
    public static void update(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCTools.getConnection();
            preparedStatement = (PreparedStatement) connection.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {

                preparedStatement.setObject(i + 1, args[i]);

            }

            preparedStatement.executeUpdate();

        } catch (Exception e) {

        } finally {
            JDBCTools.release(null, preparedStatement, connection);
        }


    }

    /**
     * 通用查询方法
     */
    public static <T> T search(Class<T> clazz, String sql, Object... args) {

        T entity = null;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //1.得到ResulSet对象
            connection = JDBCTools.getConnection();
            preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            resultSet = preparedStatement.executeQuery();

            //2.得到 ResultSetMeteData 对象
            ResultSetMetaData rSetMetaData = (ResultSetMetaData) resultSet.getMetaData();

            //3.创建一个Map<String,Object>对象，键：SQL查询列的列的别名 值：列的值
            Map<String, Object> values = new HashMap<String, Object>();

            //4.处理结果集，利用ResutSetMeteData 填充3对应的Map对象
            while (resultSet.next()) {
                //打印每一列的列名
                for (int i = 0; i < rSetMetaData.getColumnCount(); i++) {
                    String columnLabel = rSetMetaData.getCatalogName(i + 1);
                    Object columnValue = resultSet.getObject(columnLabel);
                    values.put(columnLabel, columnValue);
                }
            }

            //5.若Map不为空集，利用反射创建calzz 对应的对象,
            if (values != null) {
                Object object = clazz.newInstance();

                //遍历Map 对象，利用反射为Class对象的对应属性值赋值
                for (Map.Entry<String, Object> entry : values.entrySet()) {
                    String fieldName = entry.getKey();
                    Object fieldValue = entry.getValue();
//					ReflectionUtils.setFieldValue(object, fieldName, fieldValue);
                    BeanUtils.setProperty(object, fieldName, fieldValue);

                }

                //返回对象
                return (T) object;
            }


        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            JDBCTools.release(null, preparedStatement, connection);
        }

        return null;

    }

}

 

四、DAO设计模式

1.customer类（与数据库中customers表对应）

package com.jdbc.study;


import java.sql.Date;

/**
 * @author 
 * @create 2020-04-12 17:54
 */
public class Customer {

    private Integer id;
    private String name;
    private String email;
    private Date brith;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBrith() {
        return brith;
    }

    public void setBrith(Date brith) {
        this.brith = brith;
    }

    public Customer(Integer id, String name, String email, Date brith) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.brith = brith;
    }

    public Customer() {

    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", brith=" + brith +
                '}'+'\n';
    }
}

2.DAO接口

package com.jdbc.tools;

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

/**
 * 访问数据的 DAO 接口
 * 里边定义好访问数据表的各种方法
 *
 * @param T :DAO 处理的实体类的类型
 * @author 
 * @create 2020-04-13 15:59
 */
public interface DAO<T> {


    /**
     * 批量处理方法
     *
     * @param connection 数据库连接
     * @param sql        SQL 语句
     * @param args       填充占位符的Object [] 类型的可变参数
     */
    void batch(Connection connection, String sql, Object[]... args) throws SQLException;

    /**
     * 返回具体的一个值 例如总人数，email
     *
     * @param connection 数据库连接
     * @param sql        SQL 语句
     * @param args       填充占位符的可变参数
     * @param <E>
     * @return
     * @throws SQLException
     */
    <E> E getForValue(Connection connection, String sql, Object... args) throws SQLException;

    /**
     * 返回 T 的集合
     *
     * @param connection 数据库连接
     * @param sql        SQL 语句
     * @param args       填充占位符的可变参数
     * @return
     * @throws SQLException
     */
    List<T> getForList(Connection connection, String sql, Object... args) throws SQLException;


    /**
     * 返回一个 T 的对象
     *
     * @param connection 数据库连接
     * @param sql        SQL 语句
     * @param args       填充占位符的可变参数
     * @return
     * @throws SQLException
     */
    T get(Connection connection, String sql, Object... args) throws SQLException;


    /**
     * INSERT,UPDATE,DELETE
     *
     * @param connection : 数据库连接
     * @param sql        :SQL 语句
     * @param args       :填充占位符的可变参数
     */
    void update(Connection connection, String sql, Object... args) throws SQLException;

}

3.JdbcDaoImpl 实现类

package com.jdbc.tools;

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.sql.Connection;
import java.sql.SQLException;
import java.util.List;


/**
 * 使用 QueryRunner 提供其具体实现类
 * @param <T> 子类需传入的泛型
 */
public class JdbcDaoImpl<T> implements DAO<T> {

    private QueryRunner queryRunner = null;
    private  Class<T> type;

    public JdbcDaoImpl(){
        queryRunner = new QueryRunner();
        type = ReflectionUtils.getSuperGenericType(getClass());
    }

    @Override
    public void batch(Connection connection, String sql, Object[]... args) throws SQLException {

        queryRunner.batch(connection, sql, args);

    }

    @Override
    public <E> E getForValue(Connection connection, String sql, Object... args) throws SQLException {
        return (E)queryRunner.query(connection,sql,new ScalarHandler(),args);
    }

    @Override
    public List<T> getForList(Connection connection, String sql, Object... args) throws SQLException {
        return (List<T>)queryRunner.query(connection, sql, new BeanListHandler<>(type),args);
    }

    @Override
    public T get(Connection connection, String sql, Object... args) throws SQLException {
        return queryRunner.query(connection,sql,new BeanHandler<>(type),args);
    }

    @Override
    public void update(Connection connection, String sql, Object... args) throws SQLException {

        queryRunner.update(connection, sql, args);

    }
}

4.CustomerDao类（继承JdbcDaoImpl类）

package com.jdbc.tools;

import com.jdbc.study.Customer;

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

/**
 * @author 
 * @create 2020-04-13 16:14
 */
public class CustomerDao extends JdbcDaoImpl<Customer> {



}

测试：测试其中的Get方法和GetForValue方法

package com.jdbc.test;

import com.jdbc.study.Customer;
import com.jdbc.tools.CustomerDao;
import com.jdbc.tools.JDBCTools;
import org.junit.Test;

import java.sql.Connection;

/**
 * @author LIXICHEN
 * @create 2020-04-13 16:22
 */
public class CustomerDaoTest {

    CustomerDao customerDao = new CustomerDao();
    


    @Test
    public void testGetForValue(){
        Connection connection = null;

        try{
            connection = JDBCTools.getConnection();
            String sql = "SELECT name FROM customers WHERE id = ?";
            String customer = customerDao.getForValue(connection,sql,5);
            System.out.println(customer);
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCTools.release(null,null,connection);
        }

    }


    @Test
    public void testGet(){
        Connection connection = null;

        try{
            connection = JDBCTools.getConnection();
            String sql = "SELECT * FROM customers WHERE id = ?";
            Customer customer = customerDao.get(connection,sql,5);
            System.out.println(customer);
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCTools.release(null,null,connection);
        }

    }
}

测试结果

Get()方法

GetForValue()方法