JDBC核心技术

1. B/S架构：Browser Server 浏览器-服务器

2. 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;


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类，会执行类中的静态代码块，即注册驱动
        
        Class.forName("com.mysql.jdbc.Driver");//在mysql中该局可省略，因为引入的包包含了该操作，但是不建议省略，原因是连接其他数据库时不能省略

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

    }


    //连接方式5(最终版)：将数据库连接需要的4个基本信息声明在配置文件中，通过读取配置文件的方式获取连接
   

    @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);

    }
}

import com.JDBC.lhk.Connection.JDBC_connection;

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


public class jdbcUtils {
    
    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;
    }

    
    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 
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;


public class PrepareStatementUpdateTest {

    
    @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 
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;


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);
    }
    
    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 

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 list1 = getQuery(Account.class, sql);
        list1.forEach(System.out::println);

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

    }

    
    public T query(Class 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 List getQuery(Class 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 list = new ArrayList<>();

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

                for (int i=0;i 
四、操作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;


public class BlobTest {
    
    @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);
        }

    }

    
    @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;


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
    
    @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
    
    @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.*;


public class TransactionTest {

    
    @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;iT query(Connection coon,Class clazz,String sql,Object ...args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {

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

            //2.填充在占位符
            for (int i=0;i 
七、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;


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;iT query(Connection coon,Class clazz,String sql,Object ...args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {

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

            //2.填充在占位符
            for (int i=0;i List getQuery(Connection coon,Class clazz, String sql, Object ...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {

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

            //3.填充在占位符
            for (int i=0;i list = new ArrayList<>();

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

                for (int i=0;i E getValue(Connection coon,String sql,Object ...args) {
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
             ps = coon.prepareStatement(sql);
            for (int i=0;i 
2.实体类
 

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;


public class JdbcUtils {
    
    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;
    }

    
    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 
4.针对于account表的常用操作的接口
 
import com.jdbc.lhk.Bean.Account;

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


public interface AccountDao {

    
    void insert(Connection coon, Account account);


    
    void deleteById(Connection coon,int id);

    
    void updateById(Connection coon,Account account);

    
    Account getAccountById(Connection coon,int id);

    
    List getAll(Connection coon);


    
    Long getCount(Connection coon);


}
 
5.接口实现类
 
import com.jdbc.lhk.Bean.Account;

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


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 getAll(Connection coon) {
        String sql="select id,username,balance from account";
        List 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;


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 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数据库连接池技术
 



    
        
        com.mysql.jdbc.Driver
        jdbc:mysql://localhost:3306/test
        root
        000422

        
        
        5
        
        10
        
        10
        
        100

        
        50
        
        2

    

 
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;


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;


public class DBCP {

    
    @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);
    }

    
    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;


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;


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 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;


public class JdbcUtils {
    
    //数据库连接池只需提供一个即可。|
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("MyC3P0");
    public static Connection getConnection1() throws SQLException {
        Connection conn = cpds.getConnection();
        return conn;
    }



    
    //创建一个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;
    }

    

    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;
    }


    
    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;


public class JdbcUtils {
    
    //数据库连接池只需提供一个即可。|
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("MyC3P0");
    public static Connection getConnection1() throws SQLException {
        Connection conn = cpds.getConnection();
        return conn;
    }



    
    //创建一个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;
    }

    

    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;
    }


    
    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 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;


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);
        }
    }

    
    @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 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);
        }
    }

    
    @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 handler = new BeanListHandler<>(Account.class);
            List accounts = queryRunner.query(conn, sql, handler, 3);
            accounts.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeResource(conn,null);
        }
    }


    
    @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 map = queryRunner.query(conn, sql, handler, 4);
            System.out.println(map);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeResource(conn,null);
        }
    }


    
    @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> list = queryRunner.query(conn, sql, handler, 3);
            list.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeResource(conn,null);
        }
    }


    
    @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);
        }
    }

    
    @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 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);
        }
    }
}