java数据库开发(JDBC)

2023/10/23

后端

IDEA窗口快捷键

快捷键	描述
Ctrl+Alt+S	Settings面板
Ctrl+Shift+F/R	当前Project中全局查找/替换
Ctrl+Shif+N	文件查找面板
Alt+Insert	快速生成面板
Ctrl+Shift+A	Find Action模糊查询快速定位

代码快捷键

快捷键	描述
Ctrl+←→	上一个/下一个单词
Ctrl+Shift+Enter	自动完成
Alt+Enter	智能提示
Ctrl+Alt+L	格式化代码
Ctrl+(Shift)+/	行注释/块注释
Ctrl+Alt+Shift+J	列操作
Shift+F6	重命名(当前选择变量修改)
Ctrl+W	选中单词

代码快速定位

快捷键	描述
Ctrl+(Shift)+E	最近访问(编辑)的文件列表
Ctrl+Shift+1~9	创建书签
Shift+F11	查看书签
Ctrl+1~9	快速切换书签
Alt+←→	切换书签

Template使用

Live Templates可以添加常用快捷字母作为快捷代码

右方 + “custom”
“Abbreviation：al”
“Description：Create ArrayList”
“Template text：List< String >list = new ArrayList();”
“Template text：List< $VAR1$>$VAR2$ = new ArrayList();”
点击define设置全部

运行与打包

快捷键	描述
Shift+F9	调试
Shift+F10	运行
F8	单步运行
F9	恢复运行至下一个端点
Shift+Ctrl+F8	查看所有端点

Jar包核心配置文件设置(加载响应的Class)

将编译的类导入jar包

‘……’ compile output
Project Structure → Artifacts → Outpub Layout

设置jar包的入口类 → Create Manifest… → Main Class(Jar包加载相应目录)

生成jar包

上述操作完毕后点Build 点Build Artifacts…

IDEA快速开发Web应用

New Project → Java Enterprise → SDK1.8 = Java EE7

改变Tomcat启动时自动弹出的地址

Run/Debug Configurations → Deployment → 下方的 Application context

Project Structure

Artifacts 中默认存在 javaweb: war exploded 代表用文件夹的方式与Tomcat联动
点Add → Web Application: Archive打包右侧是待添加 [文件成功发布到jar包中 ‘javaweb’ compile output] 右侧的Web facet resources 是 jsp, html等静态资源双击放到左边[‘javaweb’module: ‘Web’ facet resources] 至此javaweb.war包就包含了所有文件。上述操作完毕后点Build 点Build Artifacts… build！！之后若在实际运行的时候放在D:\apache-tomcat-8.5.93\webapps内之后启动tomcat[D:\apache-tomcat-8.5.93\bin\startup.bat]就可以显示结果

JDBC(Java DataBase Connectivity)快速入门

JDBC作用，在java程序中与关系型数据库进行交互

JDBC优点

统一的API，提供一致的开发过程
易于学习，容易上手，代码结构稳定
功能强大，执行效率高，可处理海量数据

开发流程

1.加载并注册JDBC驱动
2.创建数据库连接
3.创建Satement对象
4.遍历查询结果
5.关闭连接.释放资源

Class.forName的作用

Class.forName用于加载指定的JDBC驱动类
Class.forName本质是通知JDBC注册这个驱动类
驱动由数据库厂商自行开发，俩厂家不同链接祖父串了，字符串也不同

创建数据库连接代码

String dbDriver = "com.mysql.cj.jdbc.Driver"; //JDBC驱动类
String dbURL = "jdbc:mysql://localhost:3306/imooc"; //连接字符串
String dbUsername =  "root";
String dbPassword = "123456";
//1.加载并初始化JDBC驱动
Class.forName(dbDriver);
//2.创建数据库连接
Connection connection = DriverManager.getConnection(dbURL,dbUsername,dbPassword);

StandardJDBCSample.java
package com.example.imoocjdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class StandardJDBCSample {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            //1.加载并注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver"); //加载指定的类
            //2.创建数据库连接
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&&allowPublicKeyRetrieval=true",
                    "root", "root"
            ); //程序和数据库的网络通信桥梁
            //3.创建Satement对象 ResultSet结果集
            Statement stmt = conn.createStatement(); //一条或多条sql语句
            ResultSet rs = stmt.executeQuery("SELECT * FROM employee WHERE dname='研发部'");
            //4.遍历查询结果
            while (rs.next()) {
                Integer eno = rs.getInt(1); //把当前行指定未知的提取 eno
                String ename = rs.getString("ename");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if (conn != null && conn.isClosed() == false) {
                    //5.关闭连接.释放资源
                    conn.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}

JDBC驱动的秘密

DriverManager

DriverManager用于注册/管理JDBC驱动程序
DriverManager.getConnection(连接字符串,用户名,密码)
返回值Connection对象,对应数据库的物理网络连接

Connection对象

Connection对象用于JDBC与数据库的网络通信对象
java.sql.Connection是一个接口，具体由驱动厂商实现
所有数据库的操作都建立在Connection上

MySQL连接字符串

格式: jdbc:mysql://[主机ip] [:端口]/数据库名?参数列表
主机ip与端口是可选设置，默认值为127.0.0.1与3306
参数列表采用url编码,格式:参数1=值1&参数2=值2

MySQL连接字符串常用参数

参数名	建议参数值	说明
useSSL	true(生产) false(开发)	是否禁用ssl
useUnicode	true	启用unicode编码传输数据
characterEncoding	UTF-8	使用UTF-8编码传输数据
serverTimezone	Asia/Shanghai	使用东8时区时间,UTC+8
allowPublicKeyRetrieval	true	允许从客户端获取公钥加密传输

超级异常捕获

选中所需要的代码区域 → Code → Surround With → 6.try…catch

SQL注入攻击 [数据泄露]

当输入部门名称：**’ or 1=1 or 1=’**
只要在or左右两侧有一个成立就都成立的输入的数据中并没有对单引号加以处理；

package com.imooc.jdbc.hrapp.command;

import java.sql.*;
import java.util.Scanner;

/**
 * 数据查询方法
 */
public class QueryCommand implements Command {
    @Override
    public void execute() {
        System.out.print("请输入部门名称：");
        Scanner in = new Scanner(System.in);
        String pdname = in.nextLine();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1. 加载并注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2. 创建数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "root");
            //3. 创建Statement对象
            stmt = conn.createStatement();
            //结果集
            System.out.println("select * from employee where dname='" + pdname + "'");
            rs = stmt.executeQuery("select * from employee where dname='" + pdname + "'");
            //4. 遍历查询结果
            //rs.next()返回布尔值，代表是否存在下一条记录
            //如果有，返回true,同时结果集提取下一条记录
            //如果没有，返回false，循环就会停止
            while (rs.next()) {
                Integer eno = rs.getInt(1);//JDBC中字段索引从1开始，而非0
                String ename = rs.getString("ename");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);

            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5. 关闭连接,释放资源
            try {
                if(rs != null){
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if(stmt != null){
                    stmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if(conn != null && !conn.isClosed() ) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

PreparedStatement[解决SQL攻击注入问题(特殊字符转义)]

SQL注入攻击

SQL注入攻击是指利用SQL漏洞越权获取数据的黑客行为
SQL注入攻击根源是未对原始SQL中的敏感字符做特殊处理
解决方法：放弃Statement改用PreparedStatement处理SQL

PreparedStatement [变化的地方用问号替代]

PreparedStatement预编译Statement是Statement的子接口
PreparedStatement对SQL进行参数化, 预防SQL注入攻击
PreparedStatement比Statement执行效率更高
要用参数化的sql语句(问号只能出现在值的地方且不能二次计算)
String sql = “select * from employee where dname=? and eno > ?“;
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,pdname); //插入多句自动寻导入上方sql语句
pstmt.setInt(2,3500);

//利用PreparedStatement预防SQL注入风险
//当dname值为' or 1=1 or 1=' 时，查询不到任何结果
//SQL:select * from employee where dname = '\' or 1=1 or 1=\”
String sql = "select * from employee where dname=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,dname); //设置SQL参数,参数从1开始
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
    ...
}

JDBC实现写数据

封装DbUtils工具类 [重复代码封装工具类(封装打开和关闭连接方法)]

DbUtils.java
package common;

import kotlin.Result;

import java.sql.*;

public class DbUtils {
    /**
     * 创建新的数据库连接
     * @return 新的Connection对象
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        //1. 加载并注册JDBC驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. 创建数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "root");
        return conn;
    }

    /**
     * 关闭连接，释放资源
     * @param rs 结果集对象
     * @param stmt Statement对象
     * @param conn Connection对象
     */
    public static void closeConnection(ResultSet rs, Statement stmt, Connection conn){
        try {
            if(rs != null){
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(stmt != null){
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(conn != null && !conn.isClosed() ) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

JDBC执行INSERT语句

String sql = "insert into employee(eno,ename) values(?,?)";
PreparedStatement pstmt = conn.PreparedStatement(sql);
pstmt.setInt(1,10);
pstmt.setString(2,"张三");
//executeUpdate方法返回记录数
int cnt = pstmt.executeUpdate(); //cnt=1
System.out.println("数据新增成功");

JDBC执行UPDATE语句

String sql = "update employee set salary = salary + 1000 where dname=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"研发部");
//executeUpdate方法返回记录数
int cnt = pstmt.exexuteUpdate();
System.out.println(“研发部”+cnt+"名员工提薪1000元");

JDBC执行DELETE语句

String sql = "delete from employee where eno = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,3395);
//executeUpdate方法返回记录数
int cnt = pstmt.executeUpdate();
System.out.println(cnt+"名员工数据已被删除");

JDBC事务管理方式

事务是以一种可靠的、一致的方式，访问和操作数据库的程序单元
说人话：要么把事情做完，要么什么都不做，不要做一半
事务依赖于数据库实现，MySQL通过事务区作为数据缓冲地带

事务的提交操作
应用程序写操作給事务区等全部完成后事务区再commit提交給数据表一次性写入給mysql。提交成功后事务区中的数据被清空

事务的回滚操作
应用程序写操作給事务区，如果+100突然-100程序报错了，由jdbc会向事务区发起rollback回滚操作清空事务区，最终数据表不会产生任何写操作[要么什么都不做，不要做一半]

JDBC两种事务模式

自动提交事务模式

自动提交模式是指每一次写操作SQL，自动提交事务

自动提交开启方法:
conn.setAutoCommit(true)

自动事务是JDBC默认行为，此模式无法保证多数据一致性[A钱少了 B钱增加]

手动提交事务模式

手动提交模式是指显式调用commit()与rollback()方法管理事务

手动提交开启方法:
conn.setAutoCommit(false)

手动提交事务可保证多数据一致性，但必须手动调用提交/回滚方法

实现批量增加员工

package test;

import common.DbUtils;

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

public class TransactionSample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try { //JDBC默认使用自动提交模式
            conn = DbUtils.getConnection();
            conn.setAutoCommit(false); //关闭自动提交
            String sql = "insert ignore into employee(eno,ename,salary,dname) values(?,?,?,?)";
            for (int i = 1000; i < 2000; i++) {
                if (i==1005){
//                    throw new RuntimeException("插入失败");
                }
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1,i);
                pstmt.setString(2,"员工" + i);
                pstmt.setFloat(3,4000f);
                pstmt.setString(4,"市场部");
                pstmt.executeUpdate();
            }
            conn.commit(); //提交数据
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if(conn != null && !conn.isClosed()) {
                    conn.rollback(); //回滚数据
                }
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        } finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }
}

基于实体类实现分页数据封装

日常开发中如果要对数据进行提取以后最常见的形式是将数据转换为对应的实体类再放到集合中进行保存，即使被关闭数据也不会丢失。

package common;

import common.entity.Employee;

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

/**
 * 分页查询员工数据
 */
public class PaginationCommand implements Command{
    @Override
    public void execute() {
        Scanner in = new Scanner(System.in);
        System.out.println("请输入页号:");
        int page = in.nextInt();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Employee> list = new ArrayList(); //将底下的信息封装到实体类中
        try {
            conn = DbUtils.getConnection(); //?前面第几行 10从这行开始向后取10条记录 limit是分页独有的方言
            String sql = "select * from employee limit ?,10";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,(page-1)*10); //第二页(2-1)*10从第十条记录开始向后取10条
            rs = pstmt.executeQuery();
            while(rs.next()){
                Integer eno = rs.getInt("eno"); //不能在原有字段1前再新增1 按照名字来获取
                String ename = rs.getString("ename");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                //JDBC获取日期使用java.sql.Date,其继承自java.util.Date
                //所以两者互相兼容
                Date hiredate = rs.getDate("hiredate");
                Employee emp = new Employee(); //每产生一条记录都要封装成Employee对象
                emp.setEno(eno);
                emp.setEname(ename);
                emp.setSalary(salary);
                emp.setDname(dname);
                emp.setHiredate(hiredate);//把每一条记录都封装成为了实体类 放入list
                list.add(emp);
            }
            System.out.println(list.size());
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(rs,pstmt,conn);
        }
    }
}

JDBC中Date日期对象的处理

package common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;

/*
   新增员工数据
 */
public class InsertCommand implements Command{
    @Override
    public void execute(){
        Scanner in = new Scanner(System.in);
        System.out.println("请输入员工编号:");
        int eno = in.nextInt();
        System.out.println("请输入员工姓名:");
        String ename = in.next();
        System.out.println("请输入员工薪资:");
        float salary = in.nextFloat();
        System.out.println("请输入隶属部门:");
        String dname = in.next();
        System.out.println("请输入入职日期:");
        String strHiredate = in.next();
        //String到java.sql.Date分为两步
        //1.前端传入的String字符串转为java.util.Date
        java.util.Date udHiredate = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            udHiredate = sdf.parse(strHiredate);//对原有字符串解析
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
        //2.java.util.Date转为java.sql.Date
        long time = udHiredate.getTime();//获取从1970到现在的毫秒数
        java.sql.Date sdHiredate = new java.sql.Date(time); //sdHiredate成功表达了strHiredate所表达的时间
        Connection conn = null;
        PreparedStatement pstmt = null;
        //新增数据 获取数据库连接
        try {
            conn = DbUtils.getConnection(); //↓作为写操作的sql一定要是参数化的(pstmt)
            String sql = "insert into employee(eno,ename,salary,dname,hiredate) value(?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql); //sql被解析
            pstmt.setInt(1,eno);
            pstmt.setString(2,ename);
            pstmt.setFloat(3,salary);
            pstmt.setString(4,dname);
            pstmt.setDate(5,sdHiredate); //目标java.sql.Date
            int cnt = pstmt.executeUpdate();//所有要改变数据表的都要使用executeUpdate
            System.out.println("cnt:" + cnt); //代表本次写入影响的记录数量
            System.out.println(ename + "员工入职手续已办理");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeConnection(null,pstmt,conn); //释放所有资源
        }
    }
}

JDBC批量处理

需要反复执行同时一次性要插入很多数据的操作使用批处理操作

BatchSample.java
package test;

import common.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class BatchSample {
    private static void tc1(){
        //标准未使用批处理
        Connection conn = null;
        PreparedStatement pstmt = null;
        try { //JDBC默认使用自动提交模式
            long startTime = new Date().getTime();
            conn = DbUtils.getConnection();
            conn.setAutoCommit(false); //关闭自动提交
            String sql = "insert ignore into employee(eno,ename,salary,dname) values(?,?,?,?)";
            for (int i = 100000; i < 200000; i++) {
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1,i);
                pstmt.setString(2,"员工" + i);
                pstmt.setFloat(3,4000f);
                pstmt.setString(4,"市场部");
                pstmt.executeUpdate();
            }
            conn.commit(); //提交数据
            long endTime = new Date().getTime();
            System.out.println("tc1()执行时长: " + (endTime-startTime));
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if(conn != null && !conn.isClosed()) {
                    conn.rollback(); //回滚数据
                }
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        } finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }

    private static void tc2(){
        //使用批处理插入若干数据
        Connection conn = null;
        PreparedStatement pstmt = null;
        try { //JDBC默认使用自动提交模式
            long startTime = new Date().getTime();
            conn = DbUtils.getConnection();
            conn.setAutoCommit(false); //关闭自动提交
            String sql = "insert ignore into employee(eno,ename,salary,dname) values(?,?,?,?)";
            for (int i = 200000; i < 300000; i++) {
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1,i);
                pstmt.setString(2,"员工" + i);
                pstmt.setFloat(3,4000f);
                pstmt.setString(4,"市场部");
//                pstmt.executeUpdate();
                pstmt.addBatch();//将参数加入批处理任务
            }
            pstmt.executeBatch();//执行批处理任务
            conn.commit(); //提交数据
            long endTime = new Date().getTime();
            System.out.println("tc2()执行时长: " + (endTime-startTime));
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if(conn != null && !conn.isClosed()) {
                    conn.rollback(); //回滚数据
                }
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        } finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }
    public static void main(String[] args) {
        tc1(); //222279ms
        tc2(); //15827 ms
    }
}

综合数据库的增删改查

HumanResourceApplication.java
package test;

import common.*;

import java.util.Scanner;

public class HumanResourceApplication {
    public static void main(String[] args) {
        System.out.println("1-查询部门员工");
        System.out.println("2-办理员工入职");
        System.out.println("3-调整薪资");
        System.out.println("4-员工离职");
        System.out.println("5-分页查询员工数据");
        System.out.println("请选择功能:");
        Scanner in = new Scanner(System.in);
        Integer cmd = in.nextInt();
        Command command = null;
        switch (cmd){
            case 1://查询部门员工
                command = new PstmtQueryCommand();
                command.execute();
                break;
            case 2:
                command = new InsertCommand();
                command.execute();
                break;
            case 3:
                command = new UpdateCommand();
                command.execute();
                break;
            case 4:
                command = new DeleteCommand();
                command.execute();
                break;
            case 5:
                command = new PaginationCommand();
                command.execute();
                break;
        }
    }
}

插入员工

InsertCommand.java
package common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;

/*
   新增员工数据
 */
public class InsertCommand implements Command{
    @Override
    public void execute(){
        Scanner in = new Scanner(System.in);
        System.out.println("请输入员工编号:");
        int eno = in.nextInt();
        System.out.println("请输入员工姓名:");
        String ename = in.next();
        System.out.println("请输入员工薪资:");
        float salary = in.nextFloat();
        System.out.println("请输入隶属部门:");
        String dname = in.next();
        System.out.println("请输入入职日期:");
        String strHiredate = in.next();
        //String到java.sql.Date分为两步
        //1.前端传入的String字符串转为java.util.Date
        java.util.Date udHiredate = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            udHiredate = sdf.parse(strHiredate);//对原有字符串解析
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
        //2.java.util.Date转为java.sql.Date
        long time = udHiredate.getTime();//获取从1970到现在的毫秒数
        java.sql.Date sdHiredate = new java.sql.Date(time); //sdHiredate成功表达了strHiredate所表达的时间
        Connection conn = null;
        PreparedStatement pstmt = null;
        //新增数据 获取数据库连接
        try {
            conn = DbUtils.getConnection(); //↓作为写操作的sql一定要是参数化的(pstmt)
            String sql = "insert into employee(eno,ename,salary,dname,hiredate) value(?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql); //sql被解析
            pstmt.setInt(1,eno);
            pstmt.setString(2,ename);
            pstmt.setFloat(3,salary);
            pstmt.setString(4,dname);
            pstmt.setDate(5,sdHiredate); //目标java.sql.Date
            int cnt = pstmt.executeUpdate();//所有要改变数据表的都要使用executeUpdate
            System.out.println("cnt:" + cnt); //代表本次写入影响的记录数量
            System.out.println(ename + "员工入职手续已办理");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeConnection(null,pstmt,conn); //释放所有资源
        }
    }
}

更新员工数据

UpdateCommand.java
package common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class UpdateCommand implements Command{
    @Override
    public void execute(){
        Scanner in = new Scanner(System.in);
        System.out.println("请输入员工编号");
        int eno = in.nextInt();
        System.out.println("请输入员工新的薪资");
        float salary = in.nextFloat();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "update employee set salary=? where dname=?";
             pstmt = conn.prepareStatement(sql);
             pstmt.setFloat(1,salary);
             pstmt.setInt(2,eno);
             int cnt = pstmt.executeUpdate();
             if (cnt == 1){
                 System.out.println("员工薪资调整完毕");
             }else {
                 System.out.println("未找到" + eno + "编号员工数据");
             }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }
}

删除员工数据

DeleteCommand.java
package common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class DeleteCommand implements Command{
    @Override
    public void execute(){
        Scanner in = new Scanner(System.in);
        System.out.println("请输入员工编号");
        int eno = in.nextInt();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "delete from employee where eno = ?";
             pstmt = conn.prepareStatement(sql);
             pstmt.setFloat(1,eno);
             int cnt = pstmt.executeUpdate();
             if (cnt == 1){
                 System.out.println("员工离职手续已完成");
             }else {
                 System.out.println("未找到" + eno + "编号员工数据");
             }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }
}

查找员工数据

PstmtQueryCommand.java
package common;

import java.sql.*;
import java.util.Scanner;

/**
 * PreparedStatement对象使用方法
 */
public class PstmtQueryCommand implements Command {

    public void execute() {
        System.out.print("请输入部门名称：");
        Scanner in = new Scanner(System.in);
        String pdname = in.nextLine();
        Connection conn = null;
//        Statement stmt = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //1. 加载并注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2. 创建数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "root");
            //3. 创建PreparedStatement对象
            String sql = "select * from employee where dname=? and eno > ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,pdname); //注意:参数索引从1
            pstmt.setInt(2,3500);
            //结果集
            rs = pstmt.executeQuery();
            //4. 遍历查询结果
            //rs.next()返回布尔值，代表是否存在下一条记录
            //如果有，返回true,同时结果集提取下一条记录
            //如果没有，返回false，循环就会停止
            while (rs.next()) {
                Integer eno = rs.getInt(1);//JDBC中字段索引从1开始，而非0
                String ename = rs.getString("ename");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);

            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5. 关闭连接,释放资源
            try {
                if(rs != null){
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if(pstmt != null){
                    pstmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if(conn != null && !conn.isClosed() ) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

分页查询员工数据

PaginationCommand.java
package common;

import common.entity.Employee;

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

/**
 * 分页查询员工数据
 */
public class PaginationCommand implements Command{
    @Override
    public void execute() {
        Scanner in = new Scanner(System.in);
        System.out.println("请输入页号:");
        int page = in.nextInt();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Employee> list = new ArrayList(); //将底下的信息封装到实体类中
        try {
            conn = DbUtils.getConnection(); //?前面第几行 10从这行开始向后取10条记录 limit是分页独有的方言
            String sql = "select * from employee limit ?,10";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,(page-1)*10); //第二页(2-1)*10从第十条记录开始向后取10条
            rs = pstmt.executeQuery();
            while(rs.next()){
                Integer eno = rs.getInt("eno"); //不能在原有字段1前再新增1 按照名字来获取
                String ename = rs.getString("ename");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                //JDBC获取日期使用java.sql.Date,其继承自java.util.Date
                //所以两者互相兼容
                Date hiredate = rs.getDate("hiredate");
                Employee emp = new Employee(); //每产生一条记录都要封装成Employee对象
                emp.setEno(eno);
                emp.setEname(ename);
                emp.setSalary(salary);
                emp.setDname(dname);
                emp.setHiredate(hiredate);//把每一条记录都封装成为了实体类 放入list
                list.add(emp);
            }
            System.out.println(list.size());
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(rs,pstmt,conn);
        }
    }
}

DbUtils【通用】

DbUtils.java
package common;

import kotlin.Result;

import java.sql.*;

public class DbUtils {
    /**
     * 创建新的数据库连接
     * @return 新的Connection对象
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        //1. 加载并注册JDBC驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. 创建数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "root");
        return conn;
    }

    /**
     * 关闭连接，释放资源
     * @param rs 结果集对象
     * @param stmt Statement对象
     * @param conn Connection对象
     */
    public static void closeConnection(ResultSet rs, Statement stmt, Connection conn){
        try {
            if(rs != null){
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(stmt != null){
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(conn != null && !conn.isClosed() ) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Employee（entity）

package common.entity;

import java.util.Date;

/**
 * 员工实体类
 */
public class Employee {
    /**
     * 1. 具备默认构造函数
     * 2. 属性私有
     * 3. 存在getter与setter
     */
    public Employee(){

    }
    //通常和数据库数据一一对应
    private Integer eno;
    private String ename;
    private Float salary;
    private String dname;
    private Date hiredate;

    public Integer getEno() {
        return eno;
    }

    public void setEno(Integer eno) {
        this.eno = eno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public Float getSalary() {
        return salary;
    }

    public void setSalary(Float salary) {
        this.salary = salary;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }
}

阿里巴巴Druid连接池[类似于施工仓库在启动应用时创建连接池]

JDBC先去创建与数据库的连接比较浪费资源和时间

Druid是阿里巴巴开源连接池组件，是最好的连接池之一
Druid对数据库连接进行有效管理与重用，最大化程序执行效率
连接池负责创建管理连接，程序只负责取用和归还

/druid-config.properties要放到resources文件夹内

druid-config.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username=root
password=root
initialSize=10 #初始数量
maxActive=20 #数据库最大连接数量
#最好初始数量=最大数量 一开始j

DruidSample.java
package test;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import common.DbUtils;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class DruidSample {
    public static void main(String[] args) {
        //1.加载属性文件
        Properties properties = new Properties();
        String propertyFile = DruidSample.class.getResource("/druid-config.properties").getPath();
        //空格->%20 会被转换
        try {
            propertyFile = new URLDecoder().decode(propertyFile,"UTF-8"); //%20还原回去
            properties.load(new FileInputStream(propertyFile));
        } catch (Exception e) {
            e.printStackTrace();
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //2.获取DataSource数据源对象
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            //3.创建数据库连接
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement("select * from employee limit 0,100");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Integer eno = rs.getInt(1);//JDBC中字段索引从1开始，而非0
                String ename = rs.getString("ename");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                System.out.println(eno + "-" + ename + "-" + salary + "-" + dname);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            DbUtils.closeConnection(rs,pstmt,conn);
        }
    }
}

扩展知识：C3P0连接池

不用属性文件，改用c3p0-config.xml保存文件

在里面&无法转义要写成 ‘ & amp; ‘

idea C3P0时出现java.sql.SQLException: No suitable driver的几种解决办法

1、对lib包Add as library
2、c3p0-config.xml必须放在source目录下，在此目录会被自动读取
3、c3p0命名必须是c3p0-config.xml（至少xml格式是这样）
4、c3p0-config.xml文件配置错误、书写错误

c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/imooc?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai&amp;allowPublicKeyRetrieval=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <!-- 连接池初始连接数量 -->
        <property name="initialPoolSize">10</property>
        <!--最大连接数量-->
        <property name="maxPoolSize">20</property>
    </default-config>
</c3p0-config>

C3P0Sample.java
package test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import common.DbUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class C3P0Sample {
    public static void main(String[] args) {
        //1.加载配置文件
        //2.创建DataSource
        DataSource dataSource = new ComboPooledDataSource();
        //3.得到数据库连接
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement("select * from employee limit 0,10");
            rs = pstmt.executeQuery();
            while(rs.next()){
                Integer eno = rs.getInt(1);//JDBC中字段索引从1开始，而非0
                String ename = rs.getString("ename");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                System.out.println(eno + "-" + ename + "-" + salary + "-" + dname);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            DbUtils.closeConnection(rs,pstmt,conn); //将数据库连接回收到连接池中而不是真正关闭
        }
    }
}

Apache Commos DBUtils

commons-dbutils是Apache提供的开源JDBC工具类库
它是对JDBC的简单封装，学习成本极低
使用commons-dbutils可以极大简化JDBC编码工作量

DbUtilsSample.java 【对数据的查询与更新】
package test;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import common.DbUtils;
import common.entity.Employee;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

/**
 * Apache DBUtils + Druid 联合使用演示
 */
public class DbUtilsSample {
    private static void query(){
        Properties properties = new Properties();
        String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            //利用Apache DbUtils大幅简化了数据的提取过程
            QueryRunner qr = new QueryRunner(dataSource); //查询执行者  ↓连接自动关闭 不用手动写代码
            List<Employee> list = qr.query("select * from employee limit ?,10",
                    new BeanListHandler<>(Employee.class),
                    new Object[]{10});//没有结果集只能new 结果自动转换成List实体类 后面的是问号赋值
            for (Employee emp : list){
                System.out.println(emp.getEname());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private static void update(){
        Properties properties = new Properties();
        String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
        Connection conn = null;
        try {
            propertyFile = new URLDecoder().decode(propertyFile,"UTF-8");
            properties.load(new FileInputStream(propertyFile)); //文件加载
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);//利用工厂类对properties信息进行载入创建对应的datasource对象
            conn = dataSource.getConnection();//获取数据库连接
            conn.setAutoCommit(false);
            String sql1 = "update employee set salary=salary+1000 where eno=?";
            String sql2 = "update employee set salary=salary-599 where eno=?";
            QueryRunner qr = new QueryRunner();
            qr.update(conn, sql1, new Object[]{1000});//写入表操作都用update
            qr.update(conn, sql2, new Object[]{1001});//分别完成加工资和减工资的操作
            conn.commit();//如果都执行成功 就提交 否则回滚
        } catch (Exception e) {
           e.printStackTrace();
            try {
                if (conn!=null && !conn.isClosed()){
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
            try {
                if (conn!=null && !conn.isClosed()){
                    conn.close(); //对数据库进行回收
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void main(String[] args) {
//        query();
        update();
    }

}

LOADING...