标签：JDBC java String connection sql import properties

JDBC

基本介绍

JDBC为访问不同的数据库提供了统一的接口，为使用者屏蔽了细节问题。
Java程序员使用JDBC,可以连接任何提供了JDBC驱动程序的数据库系统，从而完成对数据库的各种操作。

package JDBC;

import com.mysql.jdbc.Driver;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class jdbc01 {
    public static void main(String[] args) throws SQLException {
        //在项目中创建一个文件夹
        //将mysql.jar拷贝到该目录下，然后加入到项目中

        //注册驱动
        Driver driver = new Driver();

        //得到连接
        // jdbc:mysql:// 规定好的协议，通过jdbc的方式连接mysql
        // localhost IP地址
        // 3306 表示mysql监听的端口号
        // db01 表示到mysql dbms的哪个数据库
        String url = "jdbc:mysql://localhost:3306/db01";
        Properties properties = new Properties();
        properties.setProperty("user","root");   //用户
        properties.setProperty("password","root");  //密码
        Connection connect = driver.connect(url, properties);

        //执行sql
        //String sql = "insert into actor values(null,'刘德华','男','1970-11-11','123123123')";
        //String sql = "update actor set name='A' where id=1 ";
        String sql = "delete from actor where id=1 ";
        //用于执行静态SQL语句并返回其生成的结果的对象
        Statement statement = connect.createStatement();
        int rows = statement.executeUpdate(sql); //dml语句，返回值是影响的行数
        System.out.println(rows>0 ? "成功":"失败");

        //关闭连接
        statement.close();
        connect.close();

    }
}

获取数据库连接的方式

package JDBC;

import org.junit.Test;

import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class jdbc02 {
    public static void main(String[] args) {

    }

    //方式1
    @Test
    public void connect01() throws SQLException {
        Driver driver = new com.mysql.jdbc.Driver();

        String url = "jdbc:mysql://localhost:3306/db01";
        Properties properties = new Properties();
        properties.setProperty("user", "root");   //用户
        properties.setProperty("password", "root");  //密码
        Connection connect = driver.connect(url, properties);

        System.out.println(connect);
    }

    //方式2   使用反射
    @Test
    public void connect02() throws ClassNotFoundException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException, SQLException {
        //加载Driver类
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.getDeclaredConstructor().newInstance();

        String url = "jdbc:mysql://localhost:3306/db01";
        Properties properties = new Properties();
        properties.setProperty("user", "root");   //用户
        properties.setProperty("password", "root");  //密码
        Connection connect = driver.connect(url, properties);

        System.out.println(connect);
    }

    //方式3  使用DriverManager 替代 Driver 进行统一管理
    @Test
    public void connect03() throws ClassNotFoundException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException, SQLException {
        //使用反射加载Driver
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.getDeclaredConstructor().newInstance();

        String url = "jdbc:mysql://localhost:3306/db01";
        String user = "root";
        String password = "root";

        DriverManager.registerDriver(driver);//注册Driver驱动

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }

    //方式4  使用Class.forName自动完成注册驱动
    @Test
    public void connect04() throws ClassNotFoundException, SQLException {
        //使用反射加载了Driver
        // static {
        //        try {
        //            DriverManager.registerDriver(new Driver());
        //        } catch (SQLException var1) {
        //            throw new RuntimeException("Can't register driver!");
        //        }
        //    }
        //1.mysql驱动5.1.6可以无需Class.forName("com.mysql.jdbc.Driver");
        //2.从jdk1.5以后使用了jdbc4,不再需要显示调用class.forName()注册驱动而是自动调用驱动
        //   jar包下META-INF\services\java.sql.Driver 文本中的类名称去注册
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/db01";
        String user = "root";
        String password = "root";

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }

    //方式5  使用配置文件，连接数据库更加灵活
    @Test
    public void connect05() throws IOException, ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/JDBC/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }

}

ResultSet

基本介绍

表示数据库结果集的数据表，通常通过执行查询数据库的语句生成
Resultset对象保特一个光标指向基当前的数据行，最初的光标位于第一行之前
next方法将光标移动到下一行，并且由于在ResultSet对象中没有更多行时返回false，因此可以在while循环中使用循环来遍历结果集

package JDBC.testResultSet;

import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;

public class Test01 {
    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/JDBC/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);
        //得到statement
        Statement statement = connection.createStatement();
        //组织sql语句
        String sql = "select id,name,sex,borndate from actor";
        ResultSet resultSet = statement.executeQuery(sql);

        //使用while循环获取数据
        while (resultSet.next()){  //让光标向下移动
            int id = resultSet.getInt(1);//获取该行的第1列
            String name = resultSet.getString(2);//获取该行的第2列
            String sex = resultSet.getString(3);
            Date date = resultSet.getDate(4);
            System.out.println(id + "\t" + name + "\t" + sex + "\t" + date);
        }

        //关闭连接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

底层

Statement

基本介绍

Statement对象用于执行静态SQL语句并返回其生成的结果的对象
在建立连接后，需要对数据库进行访问，执行命名或是sql语句，可以通过 Statement[存在sql注入问题] PreparedStatement[预处理] CallableStatement[存储过程]
Statement对象执行sql语句，存在sql注入的风险
SQL注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的SQL语句段或命令，恶意攻击数据库。
要防范SQL注入，只要用PreparedStatement(从Statement扩展而来)取代Statement就可以了

SQL注入

public class Statement_ {
    public static void main(String[] args) throws Exception {

        Scanner scanner = new Scanner(System.in);

        //让用户输入管理员名和密码
        // 1' or
        // or '1'='1
        System.out.print("请输入管理员的名字: ");  //next(): 当接收到 空格或者 '就是表示结束
        String admin_name = scanner.nextLine(); //如果希望看到SQL注入，这里需要用nextLine
        System.out.print("请输入管理员的密码: ");
        String admin_pwd = scanner.nextLine();

        //通过Properties对象获取配置文件的信息


        Properties properties = new Properties();
        properties.load(new FileInputStream("src/JDBC/mysql.properties"));
        //获取相关的值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        //1. 注册驱动
        Class.forName(driver);//建议写上

        //2. 得到连接
        Connection connection = DriverManager.getConnection(url, user, password);

        //3. 得到Statement
        Statement statement = connection.createStatement();
      
        //4. 组织SqL
        String sql = "select name , pwd  from admin where name ='"
                + admin_name + "' and pwd = '" + admin_pwd + "'";
        ResultSet resultSet = statement.executeQuery(sql);
        if (resultSet.next()) { //如果查询到一条记录，则说明该管理存在
            System.out.println("恭喜， 登录成功");
        } else {
            System.out.println("对不起，登录失败");
        }

        //关闭连接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

PreparedStatement

基本介绍

PreparedStatement 执行的 SQL 语句中的参数用问号(?)来表示，调用Preparedstatement 对象的 setxxx()方法来设置这些参数.setxxx()方法有两个参数，第一个参数是要设置的SQL 语句中的参数的索引（从 1 开始），第二个是设置的 SOL 语句中的参数的值
调用 executeQuery()，返回 ResultSet 对象
调用 executeUpdate()，执行更新，包括增、删、修改

预处理好处

不再使用+拼接sql语句，减少语法错误
有效的解决sql注入问题
大大减少了编译的次数，效率较高

package JDBC.testPreparedStatement;

import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

public class Test01 {
    public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in);

        //让用户输入管理员名和密码
        System.out.print("请输入管理员的名字: ");  //next(): 当接收到 空格或者 '就是表示结束
        String admin_name = scanner.nextLine(); //如果希望看到SQL注入，这里需要用nextLine
        System.out.print("请输入管理员的密码: ");
        String admin_pwd = scanner.nextLine();

        //通过Properties对象获取配置文件的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/JDBC/mysql.properties"));
        //获取相关的值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);//建议写上

        Connection connection = DriverManager.getConnection(url, user, password);

        //sql语句的?相当于占位符
        String sql = "select name , pwd  from admin where name=? and pwd=? ";
        //PreparedStatement 对象 实现了PreparedStatement接口的实现类对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //给?赋值
        preparedStatement.setString(1,admin_name);
        preparedStatement.setString(2,admin_pwd);

        //这里执行，不用填sql语句 
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) { //如果查询到一条记录，则说明该管理存在
            System.out.println("恭喜， 登录成功");
        } else {
            System.out.println("对不起，登录失败");
        }

        //关闭连接
        resultSet.close();
        preparedStatement.close();
        connection.close();

    }
}

DML

package JDBC.testPreparedStatement;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import java.util.Scanner;

public class Test02 {
    public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in);

        //让用户输入管理员名和密码
        System.out.print("请输入管理员的名字: ");  //next(): 当接收到 空格或者 '就是表示结束
        String admin_name = scanner.nextLine(); //如果希望看到SQL注入，这里需要用nextLine
        //System.out.print("请输入管理员的密码: ");
        //String admin_pwd = scanner.nextLine();

        //通过Properties对象获取配置文件的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/JDBC/mysql.properties"));
        //获取相关的值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);//建议写上

        Connection connection = DriverManager.getConnection(url, user, password);

        //sql语句的?相当于占位符
        //String sql = "insert into admin values(?,?)";
        //String sql = "update admin set pwd = ? where name = ?";
        String sql = "delete from admin where name = ?";
        //PreparedStatement 对象 实现了PreparedStatement接口的实现类对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //给?赋值
        preparedStatement.setString(1,admin_name);
        //preparedStatement.setString(2,admin_pwd);

        int rows = preparedStatement.executeUpdate();
        System.out.println(rows > 0 ? "成功":"失败");

        //关闭连接
        preparedStatement.close();
        connection.close();

    }
}

JDBCAPI小结

JDBCUtils

package JDBC.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

//工具类，完成mysql的连接和关闭
public class JDBCUtils {
    private static String user;
    private static String password;
    private static String url;
    private static String driver;

    //static代码块初始化
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src/mysql.properties"));
            //读取相关的属性值
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");

        } catch (IOException e) {
            //将编译异常转成运行异常
            //可以选择捕获该异常，也可以选择默认处理该异常，比较方便
            throw new RuntimeException(e);
        }
    }

    //连接数据库，返回Connection
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //关闭相关资源
    //ResultSet结果集
    //Statement  PreparedStatement
    //Connection
    public static void close(ResultSet resultSet, Statement statement,Connection connection){
        try {
            //判断是否为空
            if (resultSet != null) {
                resultSet.close();
            }

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

            if (connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

测试

package JDBC.utils;

import jdk.nashorn.internal.ir.CallNode;
import java.sql.*;

public class Use {

    public static void main(String[] args) {
        //new Use().testDML();
        new Use().testDQL();
    }

    public void testDML(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        //组织一个sql语句
        String sql =  "update actor set name = ? where id=? ";
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //赋值
            preparedStatement.setString(1,"周星驰");
            preparedStatement.setInt(2,2);

            //执行
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }

    public void testDQL(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        String sql = "select * from actor ";
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date borndate = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");
                System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
    }
}

事务

基本介绍

JDBC程序中当一个Connection对象创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚
JDBC程序中为了让多个 SOL 语句作为一个整体执行，需要使用事务
调用 Connection 的 setAutoCommit(false）可以取消自动提交事务
在所有的 SQL 语句都成功执行后，调用 commit()：方法提交事务
在其中某个操作失败或出现异常时，调用 rollback()：方法回滚事务

package JDBC.transaction;

import JDBC.utils.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test01 {

    //不使用事务
    @Test
    public void noTransaction(){
        Connection connection = null;

        PreparedStatement preparedStatement = null;
        String sql = "update account set balance = balance-100 where id=1";
        String sql2 = "update account set balance = balance+100 where id=2";
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //抛出异常
            int i = 1/0;

            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }

    //使用事务
    @Test
    public void transaction(){
        Connection connection = null;

        PreparedStatement preparedStatement = null;
        String sql = "update account set balance = balance-100 where id=1";
        String sql2 = "update account set balance = balance+100 where id=2";
        try {
            connection = JDBCUtils.getConnection();
            connection.setAutoCommit(false);//将connection设置为不自动提交
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //抛出异常
            //int i = 1/0;

            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            //这里提交事务
            connection.commit();

        } catch (Exception throwables) {
            throwables.printStackTrace();
            //进行回滚
            System.out.println("回滚");
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }

}

批处理

基本介绍

当需要成批插入或者更新记录时。可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理
JDBC的批量处理语句包括下面方法：
addBatch()：添加需要批量处理的SQL语句或参数
executeBatch()：执行批量处理语句；
clearBatch()：清空批处理包的语句
JDBC连接MySQL时，如果要使用批处理功能，请再url中加参数 ?rewrite BatchedStatements=true
批处理往往和PreparedStatement一起搭配使用，可以既减少编译次数，又減少运行次数，效率大大提高

package JDBC;

import JDBC.utils.JDBCUtils;
import org.junit.Test;

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

public class Test1 {
    //传统方法，添加5000条数据
    @Test
    public void noBatch(){
        Connection connection = null;
        String sql = "insert into admin2 values(null,?,?)";
        PreparedStatement preparedStatement = null;

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

            System.out.println("开始执行");
            long start = System.currentTimeMillis();
            for (int i = 0; i<5000; i++){
                preparedStatement.setString(1,"A"+i);
                preparedStatement.setString(2,"666");
                preparedStatement.executeUpdate();
            }
            long end = System.currentTimeMillis();
            System.out.println("耗时: " + (end-start));//5568

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }


    }

    //使用批量方式添加数据
    //使用批处理方式需要在url添加  ?rewriteBatchedStatements=true
    @Test
    public void batch(){
        Connection connection = null;
        String sql = "insert into admin2 values(null,?,?)";
        PreparedStatement preparedStatement = null;

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

            System.out.println("开始执行");
            long start = System.currentTimeMillis();
            for (int i = 0; i<5000; i++){
                preparedStatement.setString(1,"A"+i);
                preparedStatement.setString(2,"666");
                //将sql语句加入到批处理包
                /*
                //第一次就创建ArrayList
                //elementDate => Object[] 就会存放我们预处理的sql语句
                //当elementDate 满了后，就会按1.5倍扩
                //当到达指定的值就批量处理，批量处理会减少我们发送sql语句的网络开销，而且减少网编译次数，效率提高
                   public void addBatch() throws SQLException {
        synchronized(this.checkClosed().getConnectionMutex()) {
            if (this.batchedArgs == null) {
                this.batchedArgs = new ArrayList();
            }

            for(int i = 0; i < this.parameterValues.length; ++i) {
                this.checkAllParametersSet(this.parameterValues[i], this.parameterStreams[i], i);
            }

            this.batchedArgs.add(new PreparedStatement.BatchParams(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull));
        }
    }
                 */
                preparedStatement.addBatch();
                //当有1000条数据，批量执行
                if ((i+1)%1000 == 0){
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("耗时: " + (end-start));//165

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}

底层

连接池

基本介绍

传统的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection 加载到内存中，
再验证!P地址，用户名和密码(0.05s~1s时间）。需要数据库连接的时候，就向数据库要求一个，频繁地进行数据库连接操作将占用过多系统资源，容易造成服务器崩溃
传统数据库连接，每一次数据库连接使用完后都得断开，如果程序出现异常而未能关闭，将导致数据库内存泄漏，最终将导致重启数据库
传统获取连接的方式，不能控制创建的连接数量，如连接过多，也可能导致内存泄漏，MySQL崩溃
解決传统开发中的数据库连接问题，可以采用数据库连接池技术

数据库连接池

预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕后放回去。
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新创建一个。
当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

C3P0

package JDBC.c3p0;

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

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class Test01 {
    @Test
    public void testC3P0_01() throws Exception {
        //创建一个数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

        //通过配置文件获取相关信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/mysql.properties"));
        //读取相关的属性值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        //给数据源设置相关参数
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);
        comboPooledDataSource.setJdbcUrl(url);
        //初始化连接数
        comboPooledDataSource.setInitialPoolSize(10);
        //最大连接数
        comboPooledDataSource.setMaxPoolSize(50);

        //
        Connection connection = comboPooledDataSource.getConnection();//这个方法就是从DataSource接口实现的
        System.out.println("连接成功"+connection.getClass());

        connection.close();
    }

    //使用配置文件模板来完成
    //将c3p0提供的 c3p0.config.xml 拷贝到src目录下
    //该文件指定了连接数据库和连接池的相关参数
    @Test
    public void testC3P0_02() throws SQLException {
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("my_edu");

        Connection connection = comboPooledDataSource.getConnection();
        System.out.println("连接成功");
        connection.close();


    }
}

C3P0-config

<c3p0-config>
    <!-- 数据源名称代表连接池 -->
  <named-config name="my_edu">
<!-- 驱动类 -->
  <property name="driverClass">com.mysql.jdbc.Driver</property>
  <!-- url-->
  	<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/db01</property>
  <!-- 用户名 -->
  		<property name="user">root</property>
  		<!-- 密码 -->
  	<property name="password">password</property>
  	<!-- 每次增长的连接数-->
    <property name="acquireIncrement">5</property>
    <!-- 初始的连接数 -->
    <property name="initialPoolSize">10</property>
    <!-- 最小连接数 -->
    <property name="minPoolSize">5</property>
   <!-- 最大连接数 -->
    <property name="maxPoolSize">50</property>

	<!-- 可连接的最多的命令对象数 -->
    <property name="maxStatements">5</property> 
    
    <!-- 每个连接对象可连接的最多的命令对象数 -->
    <property name="maxStatementsPerConnection">2</property>
  </named-config>
</c3p0-config>

Druid(德鲁伊)

package JDBC.Druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

public class Test01 {
    @Test
    public void testDruid() throws Exception {
        //加入druid的jar包
        //加入配置文件，将该文件拷贝到src目录下

        //创建Properties对象，读取配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/druid.properties"));

        //创建一个指定参数的数据库连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        System.out.println(dataSource.getClass());
        Connection connection = dataSource.getConnection();
        System.out.println("连接成功");
        connection.close();
    }
}

配置文件

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/girls?rewriteBatchedStatements=true
#url=jdbc:mysql://localhost:3306/girls
username=root
password=password
#initial connection Size
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=20
#max wait time (5000 mil seconds)
maxWait=5000

德鲁伊工具类

package JDBC.Druid;


import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
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 JDBCUtilsByDruid {
    private static DataSource ds;

    //在静态代码块完成初始化
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src/druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //编写getConnection方法
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    //关闭连接,在数据库连接池中，关闭连接不是真正关闭连接，而是把Connection对象放回连接池
    public static void close(ResultSet resultSet, Statement statement,Connection connection){
        try {
            if (resultSet != null){
                resultSet.close();
            }
            if (statement != null){
                statement.close();
            }
            if (connection != null){
                connection.close();
            }
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }
    
}

测试

package JDBC.Druid;

import java.sql.*;

//测试Druid工具类
public class JDBCUtilsByDruid_use {
    public static void main(String[] args) {
        Connection connection = null;
        String sql = "select*from actor";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

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

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date borndate = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");
                System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
        }
    }
}

Apache—DBUtils

土方法

package DBUtils;

import JDBC.Druid.JDBCUtilsByDruid;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;

public class Test01 {
    @Test
    public void testSelectToArrayList(){
        Connection connection = null;

        String sql = "select*from actor";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        ArrayList<Actor> list = new ArrayList<>(); //存放Actor对象
        try {
            connection = JDBCUtilsByDruid.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date borndate = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");
                //将得到的result对象的记录，封装到Actor对象，放入到list集合
                list.add(new Actor(id,name,sex,borndate,phone));
            }
            System.out.println(list);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
        }
    }
}

基本介绍

commons-dbutils是Apache组织提供的一个开源JDBC工具类库，它是对JDBC的封装，使用dbutilsi能极大简化jdbc编码的工作量

DBUtils类

QueryRunner类：该类封装了SQL的执行，是线程安全的，可以实现增、删、改、查、批处理
使用QueryRunner类实现查询
ResultSetHandler接口：该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式

DbUtils API

ArrayHandler：把结果集中的第一行数据转成对象数组
ArrayListHandler：把结果集中的每—行数据都转成一个数组，再存放到List中
BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中
BeanListHandler：将结果集中的每一行数据都封装到一个对应的Java Bean实例中，存放到List中
ColumnListHandler：将结果集中某一列的数据存放到List中
ScalarHandle：将结果集中的单行单列的数据存放到List中
KeyedHandler (name)：将结果集中的每行数据都封装到Map里，再把这些map再存到一个map里，其key为指定的key
MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对

package DBUtils;

import JDBC.Druid.JDBCUtilsByDruid;
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 org.junit.Test;

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

public class Test02 {
    //使用apache-DBUtils工具类  + Druid 完成对表的增删改查
    @Test
    public void testQueryMany() throws SQLException {  //返回结果是多行的情况
        //得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //使用JDBCUtils类和接口，先引入DBUtils相关的jar包
        //创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //query方法就是执行一个sql语句
        //new BeanListHandler<>(Actor.class) 在将resultset -> Actor对象 -> 封装到ArrayList
        //最后一个参数，是给sql语句中的?赋值
        /**
         * 分析 queryRunner.query方法:
         * public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
         *         PreparedStatement stmt = null;//定义PreparedStatement
         *         ResultSet rs = null;//接收返回的 ResultSet
         *         Object result = null;//返回ArrayList
         *
         *         try {
         *             stmt = this.prepareStatement(conn, sql);//创建PreparedStatement
         *             this.fillStatement(stmt, params);//对sql 进行 ? 赋值
         *             rs = this.wrap(stmt.executeQuery());//执行sql,返回resultset
         *             result = rsh.handle(rs);//返回的resultset --> arrayList[result] [使用到反射，对传入class对象处理]
         *         } catch (SQLException var33) {
         *             this.rethrow(var33, sql, params);
         *         } finally {
         *             try {
         *                 this.close(rs);//关闭resultset
         *             } finally {
         *                 this.close((Statement)stmt);//关闭preparedstatement对象
         *             }
         *         }
         *
         *         return result;
         *     }
         */

        //查询根据列名将其封装到Actor，Actor中的字段要和查询的字段名一致
        List<Actor> query = queryRunner.query(connection, "select * from actor where id>?", new BeanListHandler<>(Actor.class), 1);

        for (Actor actor : query){
            System.out.println(actor);
        }

        //释放资源
        //底层得到的resultset，会在query关闭，PreparedStatement也会关闭
        JDBCUtilsByDruid.close(null,null,connection);
    }

    //返回结果是单行记录
    @Test
    public void testQuerySingle() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select*from actor where id=?";

        //返回的是单行记录，所以是单个对象，使用的Handler是BeanHandler
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 2);
        System.out.println(actor);

        JDBCUtilsByDruid.close(null,null,connection);

    }

    //查询结果是单行单列的数据
    @Test
    public void testScalar() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select name from actor where id = ?";

        Object query = queryRunner.query(connection, sql, new ScalarHandler(), 2);

        System.out.println(query);

        JDBCUtilsByDruid.close(null,null,connection);

    }

    //dml操作
    @Test
    public void testDML() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();

        //String sql = "update actor set name=? where id=?";
        //String sql = "insert into actor values(null,?,?,?,?)";
        String sql = "delete from actor where id=?";
        //执行dml操作是update方法
        //返回值是受影响的行数
        //int rows = queryRunner.update(connection, sql, "张三丰", 3);
        //int rows = queryRunner.update(connection, sql, "林青霞", "女","1966-10-10","12221");
        int rows = queryRunner.update(connection,sql,5);

        System.out.println(rows>0 ? "成功":"失败");

        JDBCUtilsByDruid.close(null,null,connection);
    }
}

DAO—BasicDao

基本介绍

DAO : data access object 数据访问对象
这样的通用类，称为BasicDao，是专门和数据库交互的，即完成对数据库(表)的crud操作
在 BaiscDao 的基础上，实现一张表对应一个Dao，更好的完成功能，比如 Customer表Customer.java类(javabean)—CustomerDao.java

BasicDAO

package JDBC.dao.dao;


import JDBC.dao.utils.JDBCUtilsByDruid;
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;

//开发BasicDAO，是其他DAO的父类
public class BasicDAO<T> {  //泛型指定具体类型
    private QueryRunner queryRunner = new QueryRunner();

    //开发通用的dml方法
    public int update(String sql,Object... parameters){
        Connection connection = null;

        try {
            connection = JDBCUtilsByDruid.getConnection();
            int update = queryRunner.update(connection, sql, parameters);
            return update;
        } catch (SQLException e) {
            throw new RuntimeException(e); //将编译异常转成运行异常
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }

    //返回多个对象(查询的数据是多行的)
    public List<T> queryMulti(String sql,Class<T> clazz,Object... parameters){
        Connection connection = null;

        try {
            connection = JDBCUtilsByDruid.getConnection();

             return queryRunner.query(connection, sql, new BeanListHandler<T>(clazz), parameters);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }

    //查询单行结果的通用方法
    public T querySingle(String sql,Class<T> clazz,Object... parameters){
        Connection connection = null;

        try {
            connection = JDBCUtilsByDruid.getConnection();

            return queryRunner.query(connection,sql,new BeanHandler<T>(clazz),parameters);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }

    //查询单行单列的方法
    public Object queryScalar(String sql,Object... parameters){
        Connection connection = null;

        try {
            connection = JDBCUtilsByDruid.getConnection();

            return queryRunner.query(connection,sql,new ScalarHandler(),parameters);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }

}

ActorDAO

package JDBC.dao.dao;

import JDBC.dao.domain.Actor;

public class ActorDAO extends BasicDAO<Actor> {

}

TestDAO

package JDBC.dao.test;


import JDBC.dao.dao.ActorDAO;
import JDBC.dao.domain.Actor;
import org.junit.Test;

import java.util.List;

public class TestDAO {
    @Test
    public void testActorDAO(){
        ActorDAO actorDAO = new ActorDAO();

        //查询多行记录
        List<Actor> actors = actorDAO.queryMulti("select*from actor where id>?", Actor.class, 1);
        System.out.println("==查询结果==");
        for (Actor actor :actors) {
            System.out.println(actor);
        }

        //查询单行记录
        Actor actor = actorDAO.querySingle("select*from actor where id=?", Actor.class, 2);
        System.out.println("==查询结果==");
        System.out.println(actor);

        //查询单行单列
        Object o = actorDAO.queryScalar("select name from actor where id = ?", 2);
        System.out.println("==查询结果==");
        System.out.println(o);

        //演示dml操作
        int update = actorDAO.update("insert into actor values(null,?,?,?,?)", "张无忌", "男", "2000-1-11", "1111");
        System.out.println(update>0 ? "执行成功":"执行没有影响数据库");

    }
}

标签：JDBC,java,String,connection,sql,import,properties
From： https://www.cnblogs.com/Starry-blog/p/17047614.html

JDBC

JDBC

基本介绍

获取数据库连接的方式

ResultSet

Statement

PreparedStatement

JDBCAPI小结

JDBCUtils

事务

批处理

连接池

C3P0

Druid(德鲁伊)

德鲁伊工具类

Apache—DBUtils

DAO—BasicDao

相关文章

赞助商

阅读排行