[大数据] JDBC核心技术

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> JDBC核心技术 -> 正文阅读

[大数据]JDBC核心技术

JDBC

一、软件的架构方式

B/S架构：Browser Server 浏览器-服务器
C/S架构：Client Server 客户端-服务器
弊端：需要经常更新客户端来访问到最新修改后的信息
优势：有更好的用户体验

二、获取数据库连接


import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author TheMutents
 * @creat 2021-08-27-16:40
 */
public class JDBC_connection {

    //连接方式1
    @Test
    public void connectiontest1() throws SQLException {
        //1.提供java.sql.Driver接口实现类的对象
        Driver driver = new com.mysql.jdbc.Driver();

        //2.提供url，指明具体操作的数据库
        //  url:http://localhost:8080/gmall/keyboard.jpg
        //  jdbc:mysql  :  协议:子协议
        //  localhost : ip地址
        //  3306 : 默认mysql的端口号
        //  test : test数据库
        String url = "jdbc:mysql://localhost:3306/test";

        //3.提供Properties的对象，指明用户名和密码
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password","000422");

        //4.调用driver的connect()，获取连接
        Connection connection = driver.connect(url,info);
        System.out.println(connection);
    }

    //连接方式2：使用反射获取Driver对象，不会出现第三方的api，有更好的移植性
    @Test
    public void connectionTest2() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        //1.使用java反射机制来提供java.sql.Driver接口实现类的对象
        Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        //2.提供url，指明具体操作的数据库
        String url = "jdbc:mysql://localhost:3306/test";

        //3.提供Properties的对象，指明用户名和密码
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","000422");

        //4.调用driver的connect()，获取连接
        Connection connect = driver.connect(url, info);
        System.out.println(connect);
    }

    //连接方式3：使用DriverManager代替Driver（常用方式）
    @Test
    public void connectionTest3() throws Exception {
        //1.使用java反射机制来提供java.sql.Driver接口实现类的对象
        Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        //2.获取连接信息：url user pasaword
        String url="jdbc:mysql://localhost:3306/test";
        String user="root";
        String password="000422";

        //3.注册驱动
        DriverManager.registerDriver(driver);
        //4.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }

    //连接方式4：只加载驱动，不用显示的注册驱动
    @Test
    public void connectionTest4() throws Exception{

        //1.获取连接信息：url  user  password
        String url="jdbc:mysql://localhost:3306/test";
        String user="root";
        String password="000422";

        //2.加载Driver类，会执行类中的静态代码块，即注册驱动
        /*
        * 在mysql 的 Driver实现类中，声明了如下操作
        *  static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
                    }

        * */
        Class.forName("com.mysql.jdbc.Driver");//在mysql中该局可省略，因为引入的包包含了该操作，但是不建议省略，原因是连接其他数据库时不能省略

        //3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);

    }


    //连接方式5(最终版)：将数据库连接需要的4个基本信息声明在配置文件中，通过读取配置文件的方式获取连接
   /*
   * 该方式的好处：
   * 1.实现了数据与代码的分离，实现了解耦
   * 2.如果需要修改配置文件信息，可以避免程序重新打包
   * */

    @Test
    public void ConnectionTest5() throws IOException, ClassNotFoundException, SQLException {
        //1.读取配置文件中的4个基本信息
        InputStream resource = JDBC_connection.class.getClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros=new Properties();
        pros.load(resource);

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

        //2.加载Driver类
        Class.forName(driverClass);

        //3.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);

    }
}

三、使用PreparedStatement实现CRUD（增删改查）操作


import com.JDBC.lhk.Connection.JDBC_connection;

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

/**
 * 获取数据库连接和关闭资源
 * @author TheMutents
 * @creat 2021-09-20-13:38
 */
public class jdbcUtils {
    /**
     * 获取数据库连接
     * @return Connection
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        //1.读取配置文件中的4个基本信息
        InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros=new Properties();
        pros.load(resource);

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

        //2.加载Driver类
        Class.forName(driverClass);

        //3.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

        return connection;
    }

    /**
     * 关闭Connection和PreparedStatement
     * @param connection
     * @param ps
     */
    public static void closeResource(Connection connection, Statement ps){
        //资源关闭
        try {
            if (ps!=null)
                ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (connection!=null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void closeResource(Connection connection, Statement ps, ResultSet rs){
        //资源关闭
        try {
            if (ps!=null)
                ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (connection!=null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (rs!=null)
                rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    //通用的增删改方法
    public static int update(String sql, Object ...args)  {
        Connection coon = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库连接
            coon = jdbcUtils.getConnection();

            //2.预编译sql语句
            ps = coon.prepareStatement(sql);

            //3.填充占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //4.执行
            /**
             * *ps.execute():
             * *如果执行的是查询操作,有返回结果，则此方法返回true;
             * *如果执行的是增、删、改操作，没有返回结果，则此方法返回false.
             */
//            ps.execute();
            return ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            jdbcUtils.closeResource(coon,ps);
        }

        return 0;
    }
}


import com.JDBC.lhk.JDBCutil.jdbcUtils;
import org.junit.Test;

import java.io.InputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Properties;

/**
 * @author TheMutents
 * @creat 2021-09-19-15:54
 *
 * 使用PrepareStatement代替Statement，实现对数据库的增删改操作
 * prepareStatement可以解决拼串和sql注入问题
 * prepareStatement还可以操作Blob数据
 * 可以实现更高效的批量操作
 */
public class PrepareStatementUpdateTest {

    /**
     * 向account表插入数据
     */
    @Test
    public void insertTest() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //1.读取配置文件中的4个基本信息
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

            Properties pos = new Properties();
            pos.load(is);
            String url = pos.getProperty("url");
            String user = pos.getProperty("user");
            String password = pos.getProperty("password");
            String driverClass = pos.getProperty("driverClass");


            //2.加载Driver类
            Class<?> aClass = Class.forName(driverClass);


            //3.获取连接
           connection = DriverManager.getConnection(url, user, password);

            //4.预编译sql语句，返回PrepareStatement实例
            String sql="insert into account(username,balance,birth) values(?,?,?)";//?:占位符
            ps = connection.prepareStatement(sql);

            //5.填充占位符
            ps.setString(1,"lhk");
            ps.setDouble(2,15800);
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yy-MM-dd");
            java.util.Date date = simpleDateFormat.parse("2000-04-22");
            ps.setDate(3,new Date(date.getTime()));

            //6.执行sql
            ps.execute();//execute:执行
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        //7.资源关闭
            try {
                if (ps!=null)
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (connection!=null)
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //修改account表的一条记录
    @Test
    public void modifyTest() {
        Connection coon = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库连接
            coon = jdbcUtils.getConnection();

            //2.预编译sql语句，返回PrepareStatement实例
            String sql="update account set username=?,balance=? where id=3";
            ps = coon.prepareStatement(sql);

            //3.填充占位符
            ps.setString(1,"LHK");
            ps.setDouble(2,16800);

            //4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5.关闭资源
            jdbcUtils.closeResource(coon,ps);

        }
    }

    //通用的增删改方法
    public void update(String sql, Object ...args)  {
        Connection coon = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库连接
            coon = jdbcUtils.getConnection();

            //2.预编译sql语句
            ps = coon.prepareStatement(sql);

            //3.填充占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            jdbcUtils.closeResource(coon,ps);
        }
    }


    //测试通用的增删改方法
    @Test
    public void testCommonUpdate(){
//        //删除
//        String sql="delete from account where id = ?";
//        update(sql,3);

//        //修改
//        String sql="update stuinfo set sex=? where id=?";
//        update(sql,"f",1);

        //增加
        String sql="insert into stuinfo(id,name,sex) values(?,?,?)";
        update(sql,2,"Themutents","m");
    }

}

import com.JDBC.lhk.JDBCutil.jdbcUtils;
import com.JDBC.lhk.bean.Dept2;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

/**
 * 针对dept2表进行查询操作
 * @author TheMutents
 * @creat 2021-09-21-15:42
 */
public class dept2ForQuery {

    @Test
    public void test(){
        String sql="select department_id departmentId,department_name departmentName,manager_id managerId,location_id locationId from dept2 where department_id=?";
        Dept2 dept2 = dept2ForQuery(sql, 20);
        System.out.println(dept2);

        sql="select department_id departmentId,department_name departmentName from dept2 where department_id=?";
        Dept2 dept2_1 = dept2ForQuery(sql, 60);
        System.out.println(dept2_1);
    }
    /**
     * 针对dept2表的通用的查询方法
     *
     * 针对于表的字段名与类的属性名不相同的情况:
     * 1．必须声明sql时，使用类的属性名来命名字段的别名
     * 2．使用ResultSetMetaData时，需要使用getColumnLabel()来替换getColumnName( ),
     * 获取列的别名。
     * 说明:如果sql中没有给字段其别名，getColumnLabel()获取的就是列名
     */
    public Dept2 dept2ForQuery(String sql,Object ...args)  {
        Connection coon = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            coon = jdbcUtils.getConnection();

            //2.获取PrepareStatement实例
            ps = coon.prepareStatement(sql);

            //3.填充在占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //4.执行并返回结果集
            rs = ps.executeQuery();


            //5.处理结果集
            //5.1获取结果集的列数
            ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据：即解释结果集的相关数据
            int columnCount = rsmd.getColumnCount();//获取结果集的列数

            //5.2处理结果集每一行数据的各个列
            if (rs.next()){
                Dept2 dept2 = new Dept2();

                for (int i=0;i<columnCount;i++){
                    //获取每一列的列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取结果集每一列的列名
//                    String columnName = rsmd.getColumnName(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //通过反射机制给dept2对象指定的columnName属性，赋值为columnValue
                    //使用getColumnLabel()替换getColumnName(),来获取列的别名
                    Field field = Dept2.class.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(dept2,columnValue);
                }
                return dept2;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.关闭资源
            jdbcUtils.closeResource(coon,ps,rs);
        }

        return null;
    }



    @Test
    public void testQuery() {
        Connection coon = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //1.获取连接
            coon = jdbcUtils.getConnection();

            //2.预编译sql语句,并获取PrepareStatement实例
            String sql="select * from dept2 where department_id=?";
            ps = coon.prepareStatement(sql);

            //3.填充占位符
            ps.setObject(1,80);

            //4.执行并返回结果集
            resultSet = ps.executeQuery();

            //5.处理结果集

            if (resultSet.next()) {//next():判断结果集的下一条是否有数据，有则返回true，且指针下移，否则返回false

                int dept_id = resultSet.getInt(1);
                String dept_name = resultSet.getString(2);
                int manager_id = resultSet.getInt(3);
                int location_id = resultSet.getInt(4);

                Dept2 dept2 = new Dept2(dept_id, dept_name, manager_id, location_id);
                System.out.println(dept2);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.关闭资源
            jdbcUtils.closeResource(coon,ps,resultSet);
        }
    }
}


/**
 * 使用preparedStatement实现针对不同类的通用查询方法
 * @author TheMutents
 * @creat 2021-09-26-11:51
 */
public class PreparedSatementQuery {

    @Test
    public void test(){
        String sql="select department_id departmentId,department_name departmentName from dept2 where department_id=?";
        Dept2 d = query(Dept2.class, sql, 50);
        System.out.println(d);

        sql="select * from account where id=?";
        Account a = query(Account.class, sql, 1);
        System.out.println(a);

        System.out.println("----------------------------");
        sql="select * from account";
        List<Account> list1 = getQuery(Account.class, sql);
        list1.forEach(System.out::println);

        sql="select department_id departmentId,department_name departmentName from dept2 where department_id<?";
        List<Dept2> list2 = getQuery(Dept2.class, sql, 100);
        list2.forEach(System.out::println);

    }

    /**
     * 查询一条记录的通用方法
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T>T query(Class<T> clazz,String sql,Object ...args){
        Connection coon = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            coon = jdbcUtils.getConnection();

            //2.获取PrepareStatement实例
            ps = coon.prepareStatement(sql);

            //3.填充在占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //4.执行并返回结果集
            rs = ps.executeQuery();


            //5.处理结果集
            //5.1获取结果集的列数
            ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据：即解释结果集的相关数据
            int columnCount = rsmd.getColumnCount();//获取结果集的列数

            //5.2处理结果集每一行数据的各个列
            if (rs.next()){
                T t = clazz.newInstance();

                for (int i=0;i<columnCount;i++){
                    //获取每一列的列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取结果集每一列的列名
//                    String columnName = rsmd.getColumnName(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //通过反射机制给dept2对象指定的columnName属性，赋值为columnValue
                    //使用getColumnLabel()替换getColumnName(),来获取列的别名
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.关闭资源
            jdbcUtils.closeResource(coon,ps,rs);
        }

        return null;
        
    }

    /**
     * 查询多条记录的方法
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T> List<T> getQuery(Class<T> clazz, String sql, Object ...args) {
        Connection coon = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            coon = jdbcUtils.getConnection();

            //2.获取PrepareStatement实例
            ps = coon.prepareStatement(sql);

            //3.填充在占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //4.执行并返回结果集
            rs = ps.executeQuery();


            //5.处理结果集
            //5.1获取结果集的列数
            ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据：即解释结果集的相关数据
            int columnCount = rsmd.getColumnCount();//获取结果集的列数

            //创建集合对象
            ArrayList<T> list = new ArrayList<>();

            //5.2处理结果集每一行数据的各个列，给指定的每个t对象赋值
            while (rs.next()){
                T t = clazz.newInstance();

                for (int i=0;i<columnCount;i++){
                    //获取每一列的列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取结果集每一列的列名
//                    String columnName = rsmd.getColumnName(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //通过反射机制给dept2对象指定的columnName属性，赋值为columnValue
                    //使用getColumnLabel()替换getColumnName(),来获取列的别名
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.关闭资源
            jdbcUtils.closeResource(coon,ps,rs);
        }

        return null;

    }
}

四、操作Blob类型数据

如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。


import com.JDBC.lhk.JDBCutil.jdbcUtils;
import org.junit.Test;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * 使用PrepareStatement操作Blob数据
 * @author TheMutents
 * @creat 2021-10-01-11:20
 */
public class BlobTest {
    /**
     * 向beauty表中插入blob数据
     * 修改mysql中的全局变量：SET GLOBAL max_allowed_packet=16777216;
     * @throws Exception
     */
    @Test
    public void test()  {
        Connection connection = null;
        PreparedStatement ps = null;
        FileInputStream is=null;
        try {
            connection = jdbcUtils.getConnection();
            String sql="insert into beauty(name,sex,borndate,phone,photo,boyfriend_id) values(?,?,?,?,?,?) ";

            ps = connection.prepareStatement(sql);

            ps.setObject(1,"MM");
            ps.setObject(2,"f");
            ps.setObject(3,"1998-05-06");
            ps.setObject(4,"15677775555");
            is=new FileInputStream(new File("xc.png"));
            ps.setBlob(5,is);
            ps.setInt(6,3);

            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (is!=null){
                is.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            jdbcUtils.closeResource(connection,ps);
        }

    }

    /**
     * 查询beauty表的blob字段
     */
    @Test
    public void test1()  {
        Connection connection = null;
        PreparedStatement ps = null;
        InputStream is=null;
        FileOutputStream fos=null;
        try {
            connection = jdbcUtils.getConnection();

            String sql="select photo from beauty where id=?";

            ps = connection.prepareStatement(sql);
            ps.setInt(1,27);

            ResultSet rs = ps.executeQuery();

            if (rs.next()){

                 is = rs.getAsciiStream("photo");
                 fos = new FileOutputStream(new File("xiaochou.png"));

                int i=0;
                byte[] buff=new byte[1024];

                while((i= is.read(buff))!=-1){
                    fos.write(buff,0, i);
                }


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

            try {
                if (is!=null){
                    is.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

            try {
                if (fos!=null){
                fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        jdbcUtils.closeResource(connection,ps);

    }
}

五、批量插入

import com.JDBC.lhk.JDBCutil.jdbcUtils;
import org.junit.Test;

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

/**
 * 实现批量插入操作,使用PreparedStatement实现更高效的批量插入
 * update、 delete本身就具有批量操作的效果。
 * 此时的批量操作，主要指的是批量插入。
 * 向goods表中插入20000条数据
 * @author TheMutents
 * @creat 2021-10-01-20:57
 */
public class InsertTest {

    //方式1：使用Statement实现批量插入操作
    @Test
    public void test1() throws Exception {
        long start = System.currentTimeMillis();
        Connection connection = jdbcUtils.getConnection();
        Statement sm = connection.createStatement();
        for (int i=1;i<=20000;i++){
            String sql="insert into goods(name) values('name_+"+i+"')";
            sm.execute(sql);
        }
        long end = System.currentTimeMillis();
        System.out.println("花费时间："+(end-start)+"ms");//花费时间：19844ms
        jdbcUtils.closeResource(connection,sm);
    }


    //方式2：使用PrepareStatement实现批量插入操作
    @Test
    public void test2() throws Exception {
        long start = System.currentTimeMillis();
        Connection connection = jdbcUtils.getConnection();
        String sql="insert into goods(name) values(?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i=1;i<=20000;i++){
            ps.setString(1,"name_"+i);
            ps.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("花费时间："+(end-start)+"ms");//花费时间：19802ms
        jdbcUtils.closeResource(connection,ps);
    }

    //方式3
    /*
     * 修改1： 使用 addBatch() / executeBatch() / clearBatch()
     * 修改2：mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
     * 		 ?rewriteBatchedStatements=true 写在配置文件jdbc.properties的url后面
     * 修改3：使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
     *
     */
    @Test
    public void test3()  {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            connection = jdbcUtils.getConnection();
            String sql="insert into goods(name) values(?)";
            ps = connection.prepareStatement(sql);
            for (int i=1;i<=20000;i++){
                ps.setString(1,"name_"+i);

                //1.攒sql
                ps.addBatch();
                if(i%500==0){//攒500条sql
                    //2.执行
                    ps.executeBatch();
                    //3.清空Batch
                    ps.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("花费时间："+(end-start)+"ms");//花费时间：682ms
        } catch (Exception e) {
            e.printStackTrace();
        }
        jdbcUtils.closeResource(connection,ps);
    }

    //方式4
    /*
     * 在方式3的基础上操作
     * 使用Connection 的 setAutoCommit(false)  /  commit()
     */
    @Test
    public void test4() throws Exception {
        long start = System.currentTimeMillis();
        Connection connection = jdbcUtils.getConnection();

        //取消自动提交
        connection.setAutoCommit(false);

        String sql="insert into goods(name) values(?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i=1;i<=20000;i++){
            ps.setString(1,"name_"+i);
            //1.攒sql
            ps.addBatch();
            if (i%500==0){
                //2.执行
                ps.executeBatch();
                //3.清空
                ps.clearBatch();
            }
        }

        //提交数据
        connection.commit();

        long end = System.currentTimeMillis();
        System.out.println("花费时间："+(end-start)+"ms");//花费时间：623ms
        jdbcUtils.closeResource(connection,ps);
    }
}

六、数据库事务


import com.jdbc.lhk.Bean.Account;
import com.jdbc.lhk.utils.JdbcUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;

/**
 *事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
 *
 * 事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。
 * 当在一个事务中执行多个操作时，要么所有的事务都被提交(commit),那么这些修改就永久地保存下来；要么数据
 * 库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态
 *
 * 数据一旦提交，就不可回滚。
 *
 *4.哪些操作会导致数据的自动提交?
 * >DDL操作一旦执行,都会自动提交。
 *      >set autocommit = false对DDL操作失效
 * >DML默认情况下，—旦执行,就会自动提交。
 *      >我们可以通过set autocommit = false的方式取消DML操作的自动提交。
 * >默认在关闭连接时，会自动的提交数据
 *
 * @author TheMutents
 * @creat 2021-10-02-22:28
 */
public class TransactionTest {

    /**
     * 不考虑事务的转账操作
     * 针对account表：
     * 张无忌给赵敏转账100
     *
     */
    @Test
    public void test(){
        String sql="update account set balance=balance-100 where username='?'";
        update(sql,"张无忌");

        //遇到异常（网络异常等等）
        System.out.println(10/0);

        String sql2="update account set balance=balance+100 where username='?'";
        update(sql2,"赵敏");

        System.out.println("转账成功");
    }

    //通用的增删改方法-----1.0
    public int update(String sql, Object ...args)  {
        Connection coon = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库连接
            coon = JdbcUtils.getConnection();

            //2.预编译sql语句
            ps = coon.prepareStatement(sql);

            //3.填充占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //4.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            //5.关闭资源
            JdbcUtils.closeResource(coon,ps);
        }
        return 0;
    }


    /**
     * 考虑事务后的转账操作
     * 针对account表：
     * 张无忌给赵敏转账100
     */
    @Test
    public void test1()  {
        Connection coon = null;
        try {
            coon = JdbcUtils.getConnection();

            //1.取消自动提交
            coon.setAutoCommit(false);

            String sql="update account set balance=balance-100 where username=?";
            update(coon,sql,"张无忌");

            //遇到异常（网络异常等等）
            System.out.println(10/0);

            String sql2="update account set balance=balance+100 where username=?";
            update(coon,sql2,"赵敏");

            System.out.println("转账成功");

            //2.提交数据
            coon.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //3.遇到异常回滚数据
            try {
                coon.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        } finally {
            //恢复连接自动提交
            try {
                coon.setAutoCommit(true);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            JdbcUtils.closeResource(coon,null);
        }

    }

    //通用的增删改方法-----2.0（考虑事务）
    public int update(Connection coon,String sql, Object ...args)  {
        PreparedStatement ps = null;
        try {

            //1.预编译sql语句
            ps = coon.prepareStatement(sql);

            //2.填充占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //3.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.关闭资源,这里不关闭连接
            JdbcUtils.closeResource(null,ps);
        }
        return 0;
    }


//***********************************************************************

    /**
     * 设置数据库的隔离级别
     * */
    /*查询记录*/
    @Test
    public void test3() throws Exception {
    Connection coon= JdbcUtils.getConnection();
    //获取当前的连接的隔离级别（全局的隔离级别）
    System.out.println(coon.getTransactionIsolation());
    //设置当前数据库的隔离级别
        //coon.setTransactionIsolation();
    //取消自动提交
    coon.setAutoCommit(false);

    String sql="select id,username,balance from account where id=?";
    Account a1 = query(coon, Account.class, sql, 1);
    System.out.println(a1);
}

    /*修改记录*/
    @Test
    public void test4() throws Exception {
        Connection coon = JdbcUtils.getConnection();
        //取消自动提交
        coon.setAutoCommit(false);
        String sql="update account set balance=? where id= ?";
        update(coon, sql, 1500,1);

        Thread.sleep(15000);
        System.out.println("修改结束");



    }





    /**
     * 查询一条记录的通用方法---2.0  考虑了事务
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T>T query(Connection coon,Class<T> clazz,String sql,Object ...args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {

            //1.获取PrepareStatement实例
            ps = coon.prepareStatement(sql);

            //2.填充在占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //3.执行并返回结果集
            rs = ps.executeQuery();


            //4.处理结果集
            //4.1获取结果集的列数
            ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据：即解释结果集的相关数据
            int columnCount = rsmd.getColumnCount();//获取结果集的列数

            //4.2处理结果集每一行数据的各个列
            if (rs.next()){
                T t = clazz.newInstance();

                for (int i=0;i<columnCount;i++){
                    //获取每一列的列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取结果集每一列的列名
//                    String columnName = rsmd.getColumnName(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //通过反射机制给dept2对象指定的columnName属性，赋值为columnValue
                    //使用getColumnLabel()替换getColumnName(),来获取列的别名
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            JdbcUtils.closeResource(null,ps,rs);
        }

        return null;

    }

}

七、DAO及其相关实现类

1.BaseDao


import com.jdbc.lhk.utils.JdbcUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * DAO:
 * 封装了针对数据表的通用操作
 * @author TheMutents
 * @creat on 2021-10-08-15:21
 */
public abstract class BaseDao {

    //通用的增删改方法-----2.0（考虑事务）
    public int update(Connection coon, String sql, Object ...args)  {
        PreparedStatement ps = null;
        try {
            //1.预编译sql语句
            ps = coon.prepareStatement(sql);

            //2.填充占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //3.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.关闭资源,这里不关闭连接
            JdbcUtils.closeResource(null,ps);
        }
        return 0;
    }


    /**
     * 查询一条记录的通用方法---2.0  考虑了事务
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T>T query(Connection coon,Class<T> clazz,String sql,Object ...args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {

            //1.获取PrepareStatement实例
            ps = coon.prepareStatement(sql);

            //2.填充在占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //3.执行并返回结果集
            rs = ps.executeQuery();


            //4.处理结果集
            //4.1获取结果集的列数
            ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据：即解释结果集的相关数据
            int columnCount = rsmd.getColumnCount();//获取结果集的列数

            //4.2处理结果集每一行数据的各个列
            if (rs.next()){
                T t = clazz.newInstance();

                for (int i=0;i<columnCount;i++){
                    //获取每一列的列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取结果集每一列的列名
//                    String columnName = rsmd.getColumnName(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //通过反射机制给dept2对象指定的columnName属性，赋值为columnValue
                    //使用getColumnLabel()替换getColumnName(),来获取列的别名
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            JdbcUtils.closeResource(null,ps,rs);
        }

        return null;

    }

    /**
     * 查询多条记录的方法----2.0(考虑上事务)
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T> List<T> getQuery(Connection coon,Class<T> clazz, String sql, Object ...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {

            //2.获取PrepareStatement实例
            ps = coon.prepareStatement(sql);

            //3.填充在占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //4.执行并返回结果集
            rs = ps.executeQuery();


            //5.处理结果集
            //5.1获取结果集的列数
            ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据：即解释结果集的相关数据
            int columnCount = rsmd.getColumnCount();//获取结果集的列数

            //创建集合对象
            ArrayList<T> list = new ArrayList<>();

            //5.2处理结果集每一行数据的各个列，给指定的每个t对象赋值
            while (rs.next()){
                T t = clazz.newInstance();

                for (int i=0;i<columnCount;i++){
                    //获取每一列的列值
                    Object columnValue = rs.getObject(i + 1);

                    //获取结果集每一列的列名
//                    String columnName = rsmd.getColumnName(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //通过反射机制给dept2对象指定的columnName属性，赋值为columnValue
                    //使用getColumnLabel()替换getColumnName(),来获取列的别名
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.关闭资源
            JdbcUtils.closeResource(null,ps,rs);
        }

        return null;

    }

    //用于查询特殊值的通用方法
    public <E> E getValue(Connection coon,String sql,Object ...args) {
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
             ps = coon.prepareStatement(sql);
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            rs = ps.executeQuery();

            if(rs.next()){
                return (E) rs.getObject(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeResource(null,ps,rs);
        }
        return null;
    }
}

2.实体类


/**
 * @author TheMutents
 * @creat on 2021-10-16-22:03
 */
public class Account {
    private int id ;
    private String username;
    private double balance;

    public Account() {
    }

    public Account(int id, String username, double balance) {
        this.id = id;
        this.username = username;
        this.balance = balance;
    }

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", balance=" + balance +
                '}';
    }
}

3.工具类


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

/**
 * 获取数据库连接和关闭资源
 * @author TheMutents
 * @creat 2021-09-20-13:38
 */
public class JdbcUtils {
    /**
     * 获取数据库连接
     * @return Connection
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        //1.读取配置文件中的4个基本信息
        InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros=new Properties();
        pros.load(resource);

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

        //2.加载Driver类
        Class.forName(driverClass);

        //3.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

        return connection;
    }

    /**
     * 关闭Connection和PreparedStatement
     * @param connection
     * @param ps
     */
    public static void closeResource(Connection connection, Statement ps){
        //资源关闭
        try {
            if (ps!=null)
                ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (connection!=null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void closeResource(Connection connection, Statement ps, ResultSet rs){
        //资源关闭
        try {
            if (ps!=null)
                ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (connection!=null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (rs!=null)
                rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    //通用的增删改方法
    public static int update(String sql, Object ...args)  {
        Connection coon = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库连接
            coon = JdbcUtils.getConnection();

            //2.预编译sql语句
            ps = coon.prepareStatement(sql);

            //3.填充占位符
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            //4.执行
            /**
             * *ps.execute():
             * *如果执行的是查询操作,有返回结果，则此方法返回true;
             * *如果执行的是增、删、改操作，没有返回结果，则此方法返回false.
             */
//            ps.execute();
            return ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            JdbcUtils.closeResource(coon,ps);
        }

        return 0;
    }
}

4.针对于account表的常用操作的接口

import com.jdbc.lhk.Bean.Account;

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

/**
 * 此接口用于规范针对于account表的常常用操作
 * @author TheMutents
 * @creat on 2021-10-16-22:52
 */
public interface AccountDao {

    /**
     * 将account对象添加到数据表中
     * @param coon
     * @param account
     */
    void insert(Connection coon, Account account);


    /**
     * 根据id删除表中的一条记录
     * @param coon
     * @param id
     */
    void deleteById(Connection coon,int id);

    /**
     * 根据内存中的account的对象修改表中的指定的记录
     * @param coon
     * @param account
     */
    void updateById(Connection coon,Account account);

    /**
     * 根据指定的id从查询得到对应的Account对象
     * @param coon
     * @param id
     */
    Account getAccountById(Connection coon,int id);

    /**
     * 查询表中的所有记录构成的集合
     * @param coon
     * @return
     */
    List<Account> getAll(Connection coon);


    /**
     * 返回表中的记录条数
     * @param coon
     * @return
     */
    Long getCount(Connection coon);


}

5.接口实现类


import com.jdbc.lhk.Bean.Account;

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

/**
 * @author TheMutents
 * @creat on 2021-10-16-23:07
 */
public class AccountDaoImpl extends BaseDao implements AccountDao{
    @Override
    public void insert(Connection coon, Account account) {
        String sql ="insert into account(username,balance) values(?,?)";
        update(coon,sql,account.getUsername(),account.getBalance());

    }

    @Override
    public void deleteById(Connection coon, int id) {
        String sql="delete from account where id=?";
        update(coon,sql,id);
    }

    @Override
    public void updateById(Connection coon, Account account) {
        String sql="update account set username=?,balance=? where id=?";
        update(coon,sql,account.getUsername(),account.getBalance(),account.getId());
    }

    @Override
    public Account getAccountById(Connection coon, int id) {

        String sql="select id,username,balance from account where id=?";
        Account query = query(coon, Account.class, sql, id);
        return query;
    }

    @Override
    public List<Account> getAll(Connection coon) {
        String sql="select id,username,balance from account";
        List<Account> query = getQuery(coon, Account.class, sql);
        return query;
    }

    @Override
    public Long getCount(Connection coon) {
        String sql="select count(*) from account";
        return getValue(coon, sql);

    }
}

6.测试类


import com.jdbc.lhk.Bean.Account;
import com.jdbc.lhk.DAO.AccountDaoImpl;
import com.jdbc.lhk.utils.JdbcUtils;
import org.junit.jupiter.api.Test;

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

/**
 * @author TheMutents
 * @creat on 2021-10-19-17:49
 */
class AccountDaoImplTest {

    AccountDaoImpl accountDaoImpl=new  AccountDaoImpl();

    @Test
    void insert(){
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            Account account = new Account(3,"lhk",15000);
            accountDaoImpl.insert(connection,account);
            System.out.println("添加成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }

    @Test
    void deleteById() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            accountDaoImpl.deleteById(connection,3);
            System.out.println("删除成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }

    @Test
    void updateById() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            Account account = new Account(1,"无忌",15000);
            accountDaoImpl.updateById(connection,account);
            System.out.println("修改成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }

    @Test
    void getAccountById() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            Account account = accountDaoImpl.getAccountById(connection, 1);
            System.out.println(account);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }

    @Test
    void getAll() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            List<Account> all = accountDaoImpl.getAll(connection);
            all.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }

    @Test
    void getCount() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            Long count = accountDaoImpl.getCount(connection);
            System.out.println("account表中共有"+count+"条记录");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }
}

八、数据库连接池

1.C3P0数据库连接池技术

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>

    <named-config name="MyC3P0">
        <!-- 提供获取连接的4个基本信息  -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
        <property name="user">root</property>
        <property name="password">000422</property>

        <!--  进行数据库管理的基本信息    -->
        <!-- acquireIncrement：当数据库连接池连接数不够时，c3p0一次性向数据库服务器申请的连接数     -->
        <property name="acquireIncrement">5</property>
        <!--  initialPoolSize：c3p0数据库连接池初始化时的连接数    -->
        <property name="initialPoolSize">10</property>
        <!-- minPoolSize：c3p0数据库连接池维护的最少的连接数    -->
        <property name="minPoolSize">10</property>
        <!-- maxPoolSize：c3p0数据库连接池维护的最多的连接数     -->
        <property name="maxPoolSize">100</property>

        <!-- maxStatements：c3p0数据库连接池维护的最多的Statement的个数     -->
        <property name="maxStatements">50</property>
        <!-- maxStatementsPerConnection：每个连接中可以最多使用的Statement的个数     -->
        <property name="maxStatementsPerConnection">2</property>

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


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

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author TheMutents
 * @creat on 2021-10-20-20:47
 */
public class C3P0 {

        //方式一：
        @Test
        public void testGetConnection() throws PropertyVetoException, SQLException {
                //获取C3P0数据库连接池
                ComboPooledDataSource cpds = new ComboPooledDataSource();
                cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
                cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
                cpds.setUser("root");
                cpds.setPassword("000422");

                //通过设置相关参数，对数据库连接池进行管理
                //设置初始的数据库连接池中的连接数
                cpds.setInitialPoolSize(10);
                Connection coon = cpds.getConnection();
                System.out.println(coon);

                //销毁c3p0连接池
                DataSources.destroy( cpds );
        }

        //方式二：使用配置文件获取数据库连接池
        @Test
        public void testGetConnection1() throws SQLException {
                ComboPooledDataSource cpds = new ComboPooledDataSource("MyC3P0");
                Connection conn = cpds.getConnection();
                System.out.println(conn);
                DataSources.destroy( cpds );
        }

        }

DBCP数据库连接池技术

#基本配置信息
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
username=root
password=000422

#设置其他管理数据库连接池的相关属性
initialSize=10


import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author TheMutents
 * @creat on 2021-10-21-12:31
 */
public class DBCP {

    /**
     * 获取DBCP连接池方式一（不推荐）
     * @throws SQLException
     */
    @Test
    public void testGetConnection() throws SQLException {
        //创建DBCP的数据库连接池
        BasicDataSource source=new BasicDataSource();
        //基本配置信息
        source.setDriverClassName("com.mysql.jdbc.Driver");
        source.setUrl("jdbc:mysql:///test");
        source.setUsername("root");
        source.setPassword("000422");

        //设置其他管理数据库连接池的相关属性
        source.setInitialSize(10);
        source.setMaxActive(10);

        Connection coon = source.getConnection();
        System.out.println(coon);
    }

    /**
     *获取DBCP连接池方式二
     */
    private static Properties properties=null;
    static{
        try {
            properties = new Properties();//只创建一个Properties对象
            //读取dbcp.properties文件
            //方式一
//        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
            //方式二
            FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));

            //将流对象加载到Properties对象中
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }


    @Test
    public void testGetConnection1() throws Exception {

        //创建DBCP的数据库连接池
        DataSource source = BasicDataSourceFactory.createDataSource(properties);
        Connection coon = source.getConnection();
        System.out.println(coon);
    }

}

Druid数据库连接池技术

#基本配置信息
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
username=root
password=000422

#设置其他管理数据库连接池的相关属性
initialSize=10
maxActive=10

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

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author TheMutents
 * @creat on 2021-10-21-13:42
 */
public class Druid {

    @Test
    public void getConnection() throws Exception {
        Properties properties=new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        properties.load(is);

        DataSource source = DruidDataSourceFactory.createDataSource(properties);
        Connection conn = source.getConnection();
        System.out.println(conn);

    }
}

4.测试类


import com.jdbc.lhk.Bean.Account;
import com.jdbc.lhk.DAO.AccountDaoImpl;

import com.jdbc.lhk2.utils.JdbcUtils;
import org.junit.jupiter.api.Test;

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

/**
 * @author TheMutents
 * @creat on 2021-10-19-17:49
 */
class AccountDaoImplTest {

    AccountDaoImpl accountDaoImpl=new  AccountDaoImpl();

    @Test
    void insert(){
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection1();
            Account account = new Account(3,"lhk",15000);
            accountDaoImpl.insert(connection,account);
            System.out.println("添加成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }

    @Test
    void deleteById() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection1();
            accountDaoImpl.deleteById(connection,3);
            System.out.println("删除成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }

    @Test
    void updateById() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection1();
            Account account = new Account(1,"无忌",15000);
            accountDaoImpl.updateById(connection,account);
            System.out.println("修改成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }

    @Test
    void getAccountById() {
        Connection connection = null;
        try {
            connection = com.jdbc.lhk2.utils.JdbcUtils.getConnection3();
            Account account = accountDaoImpl.getAccountById(connection, 1);
            System.out.println(account);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }

    @Test
    void getAll() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection2();
            List<Account> all = accountDaoImpl.getAll(connection);
            all.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }

    @Test
    void getCount() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection1();
            Long count = accountDaoImpl.getCount(connection);
            System.out.println("account表中共有"+count+"条记录");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null);
        }
    }
}

5.工具类


import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

/**
 * @author TheMutents
 * @creat on 2021-10-20-21:48
 */
public class JdbcUtils {
    /**
     * 使用C3PO的数据库连接技术
     * @return
     * @throws SQLException
     */
    //数据库连接池只需提供一个即可。|
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("MyC3P0");
    public static Connection getConnection1() throws SQLException {
        Connection conn = cpds.getConnection();
        return conn;
    }



    /**
     * 使用DBCP的数据库连接技术
     * @return
     * @throws Exception
     */
    //创建一个DBCP连接池即可
    private static Properties properties=null;
    private static DataSource source=null;
    static {
        try {
            properties = new Properties();
            FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
            //将流对象加载到Properties对象中
            properties.load(is);
            source = BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection2() throws Exception {

        Connection coon = source.getConnection();
        return coon;
    }

    /**
     * 使用Druid的数据库连接技术
     * @return
     */

    private static DataSource source1;
    static {
        try {
            Properties properties=new Properties();
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);
            source1 = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection3() throws SQLException {
        Connection conn = source1.getConnection();
        return conn;
    }


    /**
     * 关闭Connection和PreparedStatement
     * @param connection
     * @param ps
     */
    public static void closeResource(Connection connection, Statement ps){
        //资源关闭
        try {
            if (ps!=null)
                ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (connection!=null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void closeResource(Connection connection, Statement ps, ResultSet rs){
        //资源关闭
        try {
            if (ps!=null)
                ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (connection!=null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (rs!=null)
                rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}

九、Apache-DBUtils实现CRUD操作

1.工具类


import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.commons.dbutils.DbUtils;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author TheMutents
 * @creat on 2021-10-20-21:48
 */
public class JdbcUtils {
    /**
     * 使用C3PO的数据库连接技术
     * @return
     * @throws SQLException
     */
    //数据库连接池只需提供一个即可。|
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("MyC3P0");
    public static Connection getConnection1() throws SQLException {
        Connection conn = cpds.getConnection();
        return conn;
    }



    /**
     * 使用DBCP的数据库连接技术
     * @return
     * @throws Exception
     */
    //创建一个DBCP连接池即可
    private static Properties properties=null;
    private static DataSource source=null;
    static {
        try {
            properties = new Properties();
            FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
            //将流对象加载到Properties对象中
            properties.load(is);
            source = BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection2() throws Exception {

        Connection coon = source.getConnection();
        return coon;
    }

    /**
     * 使用Druid的数据库连接技术
     * @return
     */

    private static DataSource source1;
    static {
        try {
            Properties properties=new Properties();
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);
            source1 = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection3() throws SQLException {
        Connection conn = source1.getConnection();
        return conn;
    }


    /**
     * 关闭Connection和PreparedStatement
     * @param connection
     * @param ps
     */
    public static void closeResource(Connection connection, Statement ps){
        //资源关闭
        try {
            if (ps!=null)
                ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (connection!=null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void closeResource(Connection connection, Statement ps, ResultSet rs){
        //资源关闭
        try {
            if (ps!=null)
                ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (connection!=null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (rs!=null)
                rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    /**
     * 使用dbutils.jar中的DbUtils工具类，实现资源的关闭
     * @param connection
     * @param ps
     * @param rs
     */
    public static void closeResource1(Connection connection, Statement ps, ResultSet rs){
//        try {
//            DbUtils.close(connection);
//            DbUtils.close(rs);
//            DbUtils.close(ps);
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(connection);
    }
}

2.测试类


import com.jdbc.lhk.Bean.Account;
import com.jdbc.lhk2.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

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

/**
 * commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它封装了针对数据库的增删改查操作，
 * 学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。
 * @author TheMutents
 * @creat on 2021-10-21-14:28
 */
public class QueryRunnerTest {

    //使用dbutils测试插入操作
    @Test
    public void testInsert()  {
        Connection conn = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            conn = JdbcUtils.getConnection3();
            String sql="insert into account(username,balance) values(?,?)";
            int insertCount = queryRunner.update(conn, sql, "lhk", 12000);
            System.out.println("成功添加了"+insertCount+"条数据");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(conn,null);
        }
    }

    /**
     * BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录
     */
    @Test
    public void query1() {
        Connection conn=null;
        try {
            QueryRunner queryRunner=new QueryRunner();
             conn = JdbcUtils.getConnection3();
            String sql="select id,username,balance from account where id=?";
            BeanHandler<Account> handler = new BeanHandler<>(Account.class);
            Account account = queryRunner.query(conn, sql, handler, 4);
            System.out.println(account);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeResource(conn,null);
        }
    }

    /**
     * BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合。
     */
    @Test
    public void query2() {
        Connection conn = null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn = JdbcUtils.getConnection3();
            String sql="select id,username,balance from account where id<?";
            BeanListHandler<Account> handler = new BeanListHandler<>(Account.class);
            List<Account> accounts = queryRunner.query(conn, sql, handler, 3);
            accounts.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeResource(conn,null);
        }
    }


    /**
     * MapHandler:是ResultSetHandler接口的实现类，对应表中的一条记录
     * 将字段及相应字段的值作为map中的key和value
     */
    @Test
    public void query3() {
        Connection conn = null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn = JdbcUtils.getConnection3();
            String sql="select id,username,balance from account where id=?";
            MapHandler handler = new MapHandler();
            Map<String, Object> map = queryRunner.query(conn, sql, handler, 4);
            System.out.println(map);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeResource(conn,null);
        }
    }


    /**
     * MapHandler:是ResultSetHandler接口的实现类，对应表中的多条记录，封装为集合
     * 将字段及相应字段的值作为map中的key和value
     */
    @Test
    public void query4() {
        Connection conn = null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn = JdbcUtils.getConnection3();
            String sql="select id,username,balance from account where id<?";
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> list = queryRunner.query(conn, sql, handler, 3);
            list.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeResource(conn,null);
        }
    }


    /**
     * ScalarHandler:用于查询特殊值
     */
    @Test
    public void query5() {
        Connection conn = null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn = JdbcUtils.getConnection3();
            String sql="select count(*) from account";
            ScalarHandler handler = new ScalarHandler();
            var count = queryRunner.query(conn, sql, handler);
            System.out.println(count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeResource(conn,null);
        }
    }

    /**
     * 自定义ResultSetHandler的实现类
     */
    @Test
    public void query6() {
        Connection conn = null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn = JdbcUtils.getConnection3();
            String sql="select id,username,balance from account where id=?";

            //匿名类实现ResultSetHandler，查询一条记录
            ResultSetHandler<Account> handler = new ResultSetHandler<>() {
                @Override
                public Account handle(ResultSet resultSet) throws SQLException {
                    if (resultSet.next()){
                        int id=resultSet.getInt("id");
                        String username=resultSet.getString("username");
                        Double balance=resultSet.getDouble("balance");
                        Account account = new Account(id, username, balance);
                        return account;
                    }
                    return null;
                }
            };

            Account account = queryRunner.query(conn, sql, handler, 4);
            System.out.println(account);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeResource(conn,null);
        }
    }
}

大数据最新文章

实现Kafka至少消费一次

亚马逊云科技：还在苦于ETL？Zero ETL的时代

初探MapReduce

【SpringBoot框架篇】32.基于注解+redis实现

Elasticsearch：如何减少 Elasticsearch 集

Go redis操作

Redis面试题

专题五 Redis高并发场景

基于GBase8s和Calcite的多数据源查询

Redis——底层数据结构原理

加:2021-10-22 11:00:15 更:2021-10-22 11:01:17

360图书馆购物三丰科技阅读网日历万年历 2024年11日历

-2024/11/24 3:46:54-

图片自动播放器
↓图片自动播放器↓

TxT小说阅读器
↓语音阅读,小说下载,古典文学↓

一键清除垃圾
↓轻轻一点,清除系统垃圾↓

图片批量下载器
↓批量下载图片,美女图库↓

网站联系: qq:121756557 email:121756557@qq.com IT数码