JDBC |封装JDBCUtils|PreparedStatement|事务|批处理|数据库连接池| Blob类型数据的读写|Apache—DBUtils简介

//通过反射，加载与注册驱动类，解耦合（不直接依赖）

Class.forName("com.mysql.jdbc.Driver");

        //1、加载与注册驱动

        Class.forName("com.mysql.jdbc.Driver");

        //2、获取数据库连接

        String url = "jdbc:mysql://localhost:3306/test";

        Connection conn = DriverManager.getConnection(url, "root", "root");

public class TestConnection {

        //增删改

        @Test

        public void testUpdate() throws  Exception {

                 //步骤1：注册驱动（为了让mysql的实现类加载到内存可以使用）

//              DriverManager.registerDriver(new Driver());

                 Class.forName("com.mysql.jdbc.Driver");

                 //步骤2：获取连接

                 Properties properties  = new Properties();//配置文件要求里面必须有连接参数和配置参数

                 properties.load(new FileInputStream("src\\druid.properties"));

                 DataSource ds =        DruidDataSourceFactory.createDataSource(properties);

                 Connection connection = ds.getConnection();

                 //步骤3：执行增删改查操作

                 //①获取执行sql语句的命令对象

                 Statement statement = connection.createStatement();

                 //②执行sql语句

//              statement.executeQuery(sql);//执行查询语句

//              statement.executeUpdate(sql)//执行增删改语法，返回受影响行数

//              statement.execute(sql)//执行任何sql语句

                 int update = statement.executeUpdate("delete from admin where id =1");

                 //③处理结果

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

                 //步骤4：关闭连接（释放资源）

                 statement.close();

                 connection.close();

        }

        //查询

        @Test

        public void testQuery() throws Exception {

                 //1.注册驱动

                 Class.forName("com.mysql.jdbc.Driver");              

                 //2.获取连接

                 Properties  pro = new Properties();

                 pro.load(new FileInputStream("src\\druid.properties"));

                 DataSource ds = DruidDataSourceFactory.createDataSource(pro);

                 Connection connection = ds.getConnection();

                 //3.执行查询

                 //①获取执行sql的命令对象

                 Statement statement = connection.createStatement();

                 //②执行sql

                 ResultSet set = statement.executeQuery("select name bname,sex gender,borndate born from beauty");

                 while(set.next()) {

                         String name = set.getString("bname");

                         char gender = set.getString("gender").charAt(0);

                         Date date = set.getDate("born");

        //              String date = set.getString("born");

                         System.out.println(name+"\t"+gender+"\t"+date);

                 }

                 //4.关闭

                 set.close();

                 statement.close();

                 connection.close();

        }

}

package com.atguigu.conn;

import java.sql.Connection;

import java.sql.DriverManager;

public class TestConnectionClose {

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

        //1、加载与注册驱动

        Class.forName("com.mysql.jdbc.Driver");

        //2、获取数据库连接

        String url = "jdbc:mysql://localhost:3306/test";

        //my.ini中max_connections=10

        for (int i = 0; i < 15; i++) {

             Connection conn = DriverManager.getConnection(url,"root", "123456");

             System.out.println(conn);

             //没有关闭，资源一直没有释放

        }

    }

}

  如果还是报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数：

  max_allowed_packet=16M

注意：修改了my.ini文件，一定要重新启动服

CREATE TABLE `user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `username` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,

  `head_picture` mediumblob,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

package com.atguigu.statement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.Scanner;

import org.junit.Test;

/*

 * Statement：

 * 1、SQL拼接

 * 2、SQL注入

 * 3、处理不了Blob类型的数据

 */

public class TestStatementProblem {

    @Test

    public void add() throws Exception{

        Scanner input = new Scanner(System.in);

        System.out.println("请输入姓名：");

        String name = input.nextLine();

        System.out.println("请输入领导编号：");

        int mid = input.nextInt();

        System.out.println("请输入部门编号：");

        int did = input.nextInt();

        //1、连接数据库

        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/1221db";

        String user = "root";

        String password = "123456";

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

        //2、创建Statement对象

        Statement st = conn.createStatement();

        //3、编写sql

        String sql = "INSERT INTO emp (ename,`mid`,did) VALUES('" + name+"'," + mid + "," + did + ")";

        //4、执行sql

        int update = st.executeUpdate(sql);

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

        //5、释放资源

        st.close();

        conn.close();

    }

    @Test

    public void select()throws Exception{

        Scanner input = new Scanner(System.in);

        System.out.println("请输入姓名：");

        String name = input.nextLine();

        //1、连接数据库

        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/1221db";

        String user = "root";

        String password = "123456";

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

        //2、写sql

        //孙红雷  ' or '1' = '1

        String sql = "SELECT eid,ename,tel,gender,salary FROM t_employee WHERE ename = '" + name + "'";

        System.out.println(sql);

//      SELECT eid,ename,tel,gender,salary FROM t_employee WHERE ename = '孙红雷  ' or '1' = '1'

        //3、用Statement执行

        Statement st = conn.createStatement();

        //4、执行查询sql

        ResultSet rs = st.executeQuery(sql);

        while(rs.next()){

             int id = rs.getInt(1);

             String ename = rs.getString(2);

             String tel = rs.getString(3);

             String gender =rs.getString(4);

             double salary = rs.getDouble(5);

             System.out.println(id+"\t" + ename + "\t" + tel + "\t" + gender + "\t" +salary);

        }

        //5、释放资源

        st.close();

        conn.close();

    }

    @Test

    public void testAddBlob(){

        String sql = "INSERT INTO `user` (username,`password`,photo)VALUES('chai','123',没法在String中处理Blob类型的数据);";

    }

}

package com.atguigu.preparedstatement;

import java.io.FileInputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.Scanner;

import org.junit.Test;

/*

 * PreparedStatement：是Statement子接口

 * 1、SQL不需要拼接

 * 2、SQL不会出现注入

 * 3、可以处理Blob类型的数据

 * tinyblob：255字节以内

 * blob：65K以内

 * mediumblob:16M以内

 * longblob：4G以内

 *

 * 如果还是报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数：

 * max_allowed_packet=16M

 * 注意：修改了my.ini文件，一定要重新启动服务

 *

 */

public class TestPreparedStatement {

    @Test

    public void add() throws Exception {

        Scanner input = new Scanner(System.in);

        System.out.println("请输入姓名：");

        String name = input.nextLine();

        System.out.println("请输入手机号码：");

        String tel = input.nextLine();

        System.out.println("请输入性别：");

        String gender = input.nextLine();

        System.out.println("请输入薪资：");

        double salary = input.nextDouble();

        System.out.println("请输入部门编号：");

        int did = input.nextInt();

        //1、连接数据库

        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/test";

        String user = "root";

        String password = "123456";

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

        //2、编写带？的SQL

        String sql = "INSERT INTO t_employee (ename,tel,gender,salary,did) VALUES(?,?,?,?,?)";

        // 3、准备一个PreparedStatement：预编译sql

        PreparedStatement pst = conn.prepareStatement(sql);// 对带？的sql进行预编译

        // 4、把?用具体的值进行代替

        pst.setString(1, name);

        pst.setString(2, tel);

        pst.setString(3, gender);

        pst.setDouble(4, salary);

        pst.setInt(5, did);

        // 5、执行sql

        int len = pst.executeUpdate();

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

        // 6、释放资源

        pst.close();

        conn.close();

    }

    @Test

    public void select() throws Exception {

        Scanner input = new Scanner(System.in);

        System.out.println("请输入姓名：");

        String name = input.nextLine();

        //1、连接数据库

        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/test";

        String user = "root";

        String password = "123456";

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

        //2、编写带?的sql

        //孙红雷  ' or '1' = '1

        String sql = "SELECT eid,ename,tel,gender,salary FROM t_employee WHERE ename = ?";

        // 3、把带？的sql语句进行预编译

        PreparedStatement pst = conn.prepareStatement(sql);

        // 4、把？用具体的变量的赋值

        pst.setString(1, name);

        // 5、执行sql

        ResultSet rs = pst.executeQuery();

        while (rs.next()) {

             int id = rs.getInt("eid");

             String ename = rs.getString("ename");

             String tel = rs.getString("tel");

             String gender = rs.getString("gender");

             double salary = rs.getDouble("salary");

             System.out.println(id + "\t" + ename + "\t" + tel + "\t" + gender + "\t" + salary);

        }

        // 6、释放资源

        rs.close();

        pst.close();

        conn.close();

    }

    @Test

    public void addBlob() throws Exception {

        Scanner input = new Scanner(System.in);

        System.out.println("请输入用户名：");

        String username = input.nextLine();

        System.out.println("请输入密码：");

        String pwd = input.nextLine();

        System.out.println("请指定照片的路径：");

        String photoPath = input.nextLine();

        //1、连接数据库

        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/test";

        String user = "root";

        String password = "123456";

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

        //2、 INSERT INTO `user` VALUES(NULL,用户名,密码,照片)

        String sql = "INSERT INTO `user` (username,`password`,head_picture)VALUES(?,?,?)";

        // 3、准备一个PreparedStatement：预编译sql

        PreparedStatement pst = conn.prepareStatement(sql);// 对带？的sql进行预编译

        // 4、对？进行设置

        pst.setString(1, username);

        pst.setString(2, pwd);

        pst.setBlob(3, new FileInputStream(photoPath));

        // 5、执行sql

        int len = pst.executeUpdate();

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

        // 6、释放资源

        pst.close();

        conn.close();

    }

}

package com.atguigu.preparedstatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.Scanner;

public class TestGenerateKey {

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

        Scanner input = new Scanner(System.in);

        System.out.println("请输入姓名：");

        String name = input.nextLine();

        System.out.println("请输入手机号码：");

        String tel = input.nextLine();

        System.out.println("请输入性别：");

        String gender = input.nextLine();

        System.out.println("请输入薪资：");

        double salary = input.nextDouble();

        System.out.println("请输入部门编号：");

        int did = input.nextInt();

        //1、连接数据库

        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/test";

        String user = "root";

        String password = "123456";

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

        //2、编写带？的SQL

        String sql = "INSERT INTO t_employee (ename,tel,gender,salary,did) VALUES(?,?,?,?,?)";

        // 3、准备一个PreparedStatement：预编译sql

//      执行添加语句，如果需要获取自增长的键值，那么在此处要告知mysql服务器，在创建PreparedStatement对象时，增加一个参数

        //autoGeneratedKeys - 指示是否应该返回自动生成的键的标志，它是 Statement.RETURN_GENERATED_KEYS 或 Statement.NO_GENERATED_KEYS 之一

        PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

        // 4、把?用具体的值进行代替

        pst.setString(1, name);

        pst.setString(2, tel);

        pst.setString(3, gender);

        pst.setDouble(4, salary);

        pst.setInt(5, did);

        // 5、执行sql

        int len = pst.executeUpdate();

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

        ResultSet rs = pst.getGeneratedKeys();

        if(rs.next()){

             System.out.println("新员工编号是：" + rs.getObject(1));

        }

        // 6、释放资源

        pst.close();

        conn.close();

    }

}

package com.atguigu.transaction;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class TestTransaction {

    public static void main(String[] args){

        Connection conn = null;

        try {

             //1、连接数据库

             Class.forName("com.mysql.jdbc.Driver");

             String url = "jdbc:mysql://localhost:3306/test";

             String user = "root";

             String password = "123456";

             conn = DriverManager.getConnection(url, user, password);

             //设置手动提交

             conn.setAutoCommit(false);

             String sql1 = "update t_department set description = ? where did = ?";

             PreparedStatement pst1 = conn.prepareStatement(sql1);

             pst1.setObject(1, "挣大钱的");

             pst1.setObject(2, 4);

             int len1 = pst1.executeUpdate();

             System.out.println(len1>0?"更新部门信息成功":"更新部门信息失败");

             pst1.close();

             String sql2 = "update t_employee set salary = salary + ? where did = ?";

             PreparedStatement pst2 = conn.prepareStatement(sql2);

             pst2.setObject(1, 20000);

             pst2.setObject(2, 4);

             int len2 = pst2.executeUpdate();

             System.out.println(len2>0?"更新部门信息成功":"更新部门信息失败");

             pst2.close();

             conn.commit();

        }catch (Exception e) {

             try {

                 if(conn!=null){

                     conn.rollback();

                 }

             } catch (SQLException e1) {

                 e1.printStackTrace();

             }

        } finally{

             try {

                 if(conn!=null){

                     //恢复自动提交

                     conn.setAutoCommit(true);

                     //释放连接

                     conn.close();

                 }

             } catch (SQLException e) {

                 e.printStackTrace();

             }

        }

    }

}

package com.atguigu.druid;

import java.sql.Connection;

import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class TestDruid {

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

        Properties pro = new Properties();

        pro.load(TestDruid.class.getClassLoader().getResourceAsStream("druid.properties"));

        DataSource ds = DruidDataSourceFactory.createDataSource(pro);

        Connection conn = ds.getConnection();

        System.out.println(conn);

    }

}

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

username=root

password=123456

driverClassName=com.mysql.jdbc.Driver

initialSize=10

maxActive=20

maxWait=1000

filters=wall

name

配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this)

jdbcUrl

连接数据库的url，不同数据库不一样。例如：mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto

username

连接数据库的用户名

password

连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。详细看这里：https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilter

driverClassName

根据url自动识别 这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下)

initialSize

0

初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时

maxActive

8

最大连接池数量

maxIdle

8

已经不再使用，配置了也没效果

minIdle

最小连接池数量

maxWait

获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。

poolPreparedStatements

false

是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。

maxOpenPreparedStatements

-1

要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100

validationQuery

用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。

testOnBorrow

true

申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。

testOnReturn

false

归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能

testWhileIdle

false

建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。

timeBetweenEvictionRunsMillis

有两个含义： 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据，详细看testWhileIdle属性的说明

numTestsPerEvictionRun

不再使用，一个DruidDataSource只支持一个EvictionRun

minEvictableIdleTimeMillis

connectionInitSqls

物理连接初始化的时候执行的sql

exceptionSorter

根据dbType自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接

filters

属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall

proxyFilters

类型是List，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系

package com.atguigu.util;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

/*

 * 获取连接或释放连接的工具类

 */

public class JDBCTools {

    // 1、数据源,即连接池

    private static DataSource dataSource;

    // 2、ThreadLocal对象

    private static ThreadLocal<Connection> threadLocal;

    static {

        try {

             //1、读取druip.properties文件

             Properties pro = new Properties();

             pro.load(JDBCTools.class.getClassLoader().getResourceAsStream("druid.properties"));

             //2、连接连接池

             dataSource = DruidDataSourceFactory.createDataSource(pro);

             //3、创建线程池

             threadLocal = new ThreadLocal<>();

        } catch (Exception e) {

             e.printStackTrace();

        }

    }

    /**

     * 获取连接的方法

     *

     * @return

     * @throws SQLException

     */

    public static Connection getConnection() {

        // 从当前线程中获取连接

        Connection connection = threadLocal.get();

        if (connection == null) {

             // 从连接池中获取一个连接

             try {

                 connection = dataSource.getConnection();

                 // 将连接与当前线程绑定

                 threadLocal.set(connection);

             } catch (SQLException e) {

                 e.printStackTrace();

             }

        }

        return connection;

    }

    /**

     * 释放连接的方法

     *

     * @param connection

     */

    public static void releaseConnection() {

        // 获取当前线程中的连接

        Connection connection = threadLocal.get();

        if (connection != null) {

             try {

                 connection.close();

                 // 将已经关闭的连接从当前线程中移除

                 threadLocal.remove();

             } catch (SQLException e) {

                 e.printStackTrace();

             }

        }

    }

}

int,double等在Java中都用包装类，因为mysql中的所有类型都可能是NULL，而Java只有引用数据类型才有NULL值

public class Employee {

        private Integer eid;

        private String ename;

        private String tel;

        private String gender;//mysql中用char,在Java中也要用String

        private Double salary;

        private Double commissionPct;

        private Date birthday;//此处用String或Date

        private Date hiredate;

        private Integer jobId;

        private String email;

        private Integer mid;

        private String address;

        private String nativePlace;

        private Integer did;

...

}