欢迎来到wjc的博客

学习，不知道我几斤几两，不终生学习，被淘汰的就是我。不

JDBC

JDBC

1.使用目的

​ 我们要做到的是同一套Java代码操作不同的关系型数据库，而此时sun公司就指定了一套标准接口（JDBC），JDBC中定义了所有操作关系型数据库的规则。可随时替换底层数据库，访问数据库的Java代码基本不变。

2.安装mysql，JDBC

3.使用

​ 1.注册驱动（mysql5之后不需要）

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

​ 2.获取连接

String url = "jdbc:mysql://localhost:3306/student";
String url1 = "jdbc:mysql://127.0.0.1:3306/student";
String url2 = "jdbc:mysql://192.168.43.64:3306/student";
注意：使用url2时，需要在mysql下执行
    要想让别人远程访问自己的数据库
    use mysql;
	update user set host ='%' where user = "root" and host ="localhost";
	flush privileges;
Connection conn=DriverManager.getConnection(url,username,password);

​ 3.定义sql语句

String sql = "select * from 表名"

​ 执行SQL语句需要SQL执行对象，而这个执行对象就是Statement对象

​ 4.获取sql对象

Statement stmt = conn.createStatement();

​ 5.执行sql

stmt.excute(sql);

​ 6.处理返回结果

​ 7.释放资源

stmt.close();
conn.close();

4.注意

DriverMAnager：获取数据库连接

connection：获取执行sql的对象，管理事务

statement：执行sql：DDL，DML，DQL（ResultSet）

ResultSet：封装了SQL查询语句的结果，常用方法：next()，getXXX()

5.事务管理

开启事务：BEGIN 或者 START TRANSACTION;

提交事务：COMMIT;

回滚事务：ROLLBACK;

/**
 * JDBC API 详解：Connection
 */
public class JDBCDemo3_Connection {
 
    public static void main(String[] args) throws Exception {
        //1. 注册驱动
        //Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
        String url = "jdbc:mysql:///db1?useSSL=false";
        String username = "root";
        String password = "1234";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3. 定义sql
        String sql1 = "update account set money = 3000 where id = 1";
        String sql2 = "update account set money = 3000 where id = 2";
        //4. 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();
 
        try {
            // ============开启事务==========
            conn.setAutoCommit(false);
            //5. 执行sql
            int count1 = stmt.executeUpdate(sql1);//受影响的行数
            //6. 处理结果
            System.out.println(count1);
            int i = 3/0;
            //5. 执行sql
            int count2 = stmt.executeUpdate(sql2);//受影响的行数
            //6. 处理结果
            System.out.println(count2);
 
            // ============提交事务==========
            //程序运行到此处，说明没有出现任何问题，则需求提交事务
            conn.commit();
        } catch (Exception e) {
            // ============回滚事务==========
            //程序在出现异常时会执行到这个地方，此时就需要回滚事务
            conn.rollback();
            e.printStackTrace();
        }
 
        //7. 释放资源
        stmt.close();
        conn.close();
    }
}

6.Rsultset使用

/**
  * 查询account账户表数据，封装为Account对象中，并且存储到ArrayList集合中
  * 1. 定义实体类Account
  * 2. 查询数据，封装到Account对象中
  * 3. 将Account对象存入ArrayList集合中
  */
@Test
public void testResultSet2() throws  Exception {
    //1. 注册驱动
    //Class.forName("com.mysql.jdbc.Driver");
    //2. 获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);
 
    //3. 定义sql
    String sql = "select * from account";
 
    //4. 获取statement对象
    Statement stmt = conn.createStatement();
 
    //5. 执行sql
    ResultSet rs = stmt.executeQuery(sql);
 
    // 创建集合
    List<Account> list = new ArrayList<>();
   
    // 6.1 光标向下移动一行，并且判断当前行是否有数据
    while (rs.next()){
        Account account = new Account();
 
        //6.2 获取数据  getXxx()
        int id = rs.getInt("id");
        String name = rs.getString("name");
        double money = rs.getDouble("money");
 
        //赋值
        account.setId(id);
        account.setName(name);
        account.setMoney(money);
 
        // 存入集合
        list.add(account);
    }
 
    System.out.println(list);
 
    //7. 释放资源
    rs.close();
    stmt.close();
    conn.close();
}

7.防止sql注入（PreparedStatement）

​ SQL注入：通过操作输入来修改事先定义好的SQL语句，用以达到执行代码对服务器进行攻击的方法

​ SQL注入场景：有一个用户表，在登入时，需要使用sql语句查询表中的用户名和密码与输入的用户名和密码是否匹配

@Test
public void testLogin() throws  Exception {
    //2. 获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);
 
    // 接收用户输入 用户名和密码
    String name = "sjdljfld";
    String pwd = "' or '1' = '1";
    String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";
    // 获取stmt对象
    Statement stmt = conn.createStatement();
    // 执行sql
    ResultSet rs = stmt.executeQuery(sql);
    // 判断登录是否成功
    if(rs.next()){
        System.out.println("登录成功~");
    }else{
        System.out.println("登录失败~");
    }
 
    //7. 释放资源
    rs.close();
    stmt.close();
    conn.close();
}

​ 解决sql注入：将SQL执行对象 Statement 换成 PreparedStatement 对象。本质：进行转义

@Test
public void testPreparedStatement() throws  Exception {
    //2. 获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);
 
    // 接收用户输入 用户名和密码
    String name = "zhangsan";
    String pwd = "' or '1' = '1";
 
    // 定义sql
    String sql = "select * from tb_user where username = ? and password = ?";
    // 获取pstmt对象
    PreparedStatement pstmt = conn.prepareStatement(sql);
    // 设置？的值
    pstmt.setString(1,name);
    pstmt.setString(2,pwd);
    // 执行sql
    ResultSet rs = pstmt.executeQuery();
    // 判断登录是否成功
    if(rs.next()){
        System.out.println("登录成功~");
    }else{
        System.out.println("登录失败~");
    }
    //7. 释放资源
    rs.close();
    pstmt.close();
    conn.close();
}

8.数据库连接池使用

官方提供标准接口：DataSource获取Connection

​ 那么以后就不需要通过 DriverManager 对象获取 Connection 对象，而是通过连接池（DataSource）获取 Connection 对象

常见的数据库连接池：DBCP，C3P0，Druid

使用Driud

/**
 * Druid数据库连接池演示
 */
public class DruidDemo {
 
    public static void main(String[] args) throws Exception {
        //1.导入jar包
        //2.定义配置文件
        //3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
        //4. 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
 
        //5. 获取数据库连接 Connection
        Connection connection = dataSource.getConnection();
        System.out.println(connection); //获取到了连接后就可以继续做其他操作了
 
        //System.out.println(System.getProperty("user.dir"));
    }
}

9.使用JDBC实现mysql的增删改查

9.1环境准备

-- 创建student表
create table student(
	id int,
    name varchar(10),
    birthday date,
    score double(5,2),
    email varchar(64),
    tel varchar(15),
    status tinyint
);
-- 添加数据
insert into student (id,name,birthday,score,email,tel,status) 
values (2020,"wjc1","2020-01-01",120.5,"120.com","150274-681",0),
		(2021,"wjc2","2021-01-01",130.5,"130.com","150245-681",1),
		(2022,"wjc3","2022-01-01",140.5,"140.com","150212-681",0),
		(2023,"wjc4","2023-01-01",150.5,"150.com","150296-681",1);

9.2构建Student类

class student{
    int id;
    String name;
    java.sql.Date birthday;
    double score;
    String email;
    String tel;
    int stutus;
 
    public student(int id,String name, java.sql.Date birthday, double score, String email, String tel, int stutus){
        this.id=id;
        this.name=name;
        this.birthday=birthday;
        this.score=score;
        this.email=email;
        this.tel=tel;
        this.stutus=stutus;
    }
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public Date getBirthday() {
        return birthday;
    }
 
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
 
    public double getScore() {
        return score;
    }
 
    public void setScore(double score) {
        this.score = score;
    }
 
    public String getEmail() {
        return email;
    }
 
    public void setEmail(String email) {
        this.email = email;
    }
 
    public String getTel() {
        return tel;
    }
 
    public void setTel(String tel) {
        this.tel = tel;
    }
 
    public int getStutus() {
        return stutus;
    }
 
    public void setStutus(int stutus) {
        this.stutus = stutus;
    }
 
    @Override
    public String toString() {
        return "student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", birthday=" + birthday +
                ", score=" + score +
                ", email='" + email + '\'' +
                ", tel='" + tel + '\'' +
                ", stutus=" + stutus +
                '}';
    }
}

9.3查询所有

//          查询数据
    public static void SelectAllData() throws Exception {
//        加载配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("java_web_JDBC/src/druid.properties"));
//        获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//        获取数据库连接
        Connection connection = dataSource.getConnection();
//        定义sql
        String sql = "select * from student";
//        使用PPreparedStatement防注入,获取pstmt
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
//        执行sql
        ResultSet resultSet = preparedStatement.executeQuery();
//        将获取的数据封装成Student对象
        ArrayList<student> st = new ArrayList<>();
//        获取数据
        while (resultSet.next()){
            int id=resultSet.getInt("id");
            String name=resultSet.getString("name");
            java.sql.Date birthday=resultSet.getDate("birthday");
            double score=resultSet.getDouble("score");
            String email=resultSet.getString("email");
            String tel=resultSet.getString("tel");
            int stutus=resultSet.getInt("status");
//            分装成student对象
            student student = new student();
            student.setId(id);
            student.setName(name);
            student.setBirthday(birthday);
            student.setScore(score);
            student.setEmail(email);
            student.setTel(tel);
            student.setStutus(stutus);
//            将封装的对象放入集合
            st.add(student);
        }
        System.out.println(st);
//       释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

9.4添加数据

public static  void AddData(Connection connection) throws Exception {
        String sql ="insert into student (id,name,birthday,score,email,tel,status) values (?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,2025);
        preparedStatement.setString(2,"wjc5");
 
        String stringDate="2024-01-01";
        preparedStatement.setDate(3,new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse(stringDate).getTime()));
 
        preparedStatement.setDouble(4,160.5);
        preparedStatement.setString(5,"160.com");
        preparedStatement.setString(6,"1568999-456");
        preparedStatement.setInt(7,0);
 
        int count = preparedStatement.executeUpdate();
        System.out.println(count > 0);
 
//        preparedStatement.close();
//        connection.close();
 
 
    }

9.5修改数据

public static void UpdateData(Connection connection) throws Exception {
        String sql="update student set name=?,birthday=?,score=?,email=?,tel=?,status=? where id =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"wjc25");
 
        String stringDate="2025-01-01";
        preparedStatement.setDate(2,new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse(stringDate).getTime()));
 
        preparedStatement.setDouble(3,250.5);
        preparedStatement.setString(4,"250.com");
        preparedStatement.setString(5,"1568988-456");
        preparedStatement.setInt(6,1);
        preparedStatement.setInt(7,2025);
        int count = preparedStatement.executeUpdate();
        System.out.println(count>0);
 
//        preparedStatement.close();
//        connection.close();
 
 
    }

9.6删除数据

public static void DeleteData(Connection connection) throws Exception{
        String sql="delete from student where id =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,2025);
        int count = preparedStatement.executeUpdate();
        System.out.println(count>0);
 
        preparedStatement.close();
        connection.close();
 
 
    }

9.7汇总：

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Properties;
 
public class JDBC6 {
//    JDBC操作数据库实现增删改查
    public static void main(String[] args) throws Exception {
        //        加载配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("java_web_JDBC/src/druid.properties"));
        //        获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        //        获取数据库连接
        Connection connection = dataSource.getConnection();
 
 
 
//          查询数据
        SelectAllData(connection);
//          添加数据
        AddData(connection);
//          修改数据
        UpdateData(connection);
//          删除数据
        DeleteData(connection);
 
 
 
    }
 
 
//          查询数据
    public static void SelectAllData(Connection connection) throws Exception {
 
//        定义sql
        String sql = "select * from student";
//        使用PPreparedStatement防注入,获取pstmt
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
//        执行sql
        ResultSet resultSet = preparedStatement.executeQuery();
//        将获取的数据封装成Student对象
        ArrayList<student> st = new ArrayList<>();
//        获取数据
        while (resultSet.next()){
            int id=resultSet.getInt("id");
            String name=resultSet.getString("name");
            java.sql.Date birthday=resultSet.getDate("birthday");
            double score=resultSet.getDouble("score");
            String email=resultSet.getString("email");
            String tel=resultSet.getString("tel");
            int stutus=resultSet.getInt("status");
//            分装成student对象
            student student = new student();
            student.setId(id);
            student.setName(name);
            student.setBirthday(birthday);
            student.setScore(score);
            student.setEmail(email);
            student.setTel(tel);
            student.setStutus(stutus);
//            将封装的对象放入集合
            st.add(student);
        }
        System.out.println(st);
 
        //       释放资源
        resultSet.close();
//        preparedStatement.close();
//        connection.close();
 
    }
 
//          添加数据
    public static  void AddData(Connection connection) throws Exception {
        String sql ="insert into student (id,name,birthday,score,email,tel,status) values (?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,2025);
        preparedStatement.setString(2,"wjc5");
 
        String stringDate="2024-01-01";
        preparedStatement.setDate(3,new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse(stringDate).getTime()));
 
        preparedStatement.setDouble(4,160.5);
        preparedStatement.setString(5,"160.com");
        preparedStatement.setString(6,"1568999-456");
        preparedStatement.setInt(7,0);
 
        int count = preparedStatement.executeUpdate();
        System.out.println(count > 0);
 
//        preparedStatement.close();
//        connection.close();
 
 
    }
//          修改数据
    public static void UpdateData(Connection connection) throws Exception {
        String sql="update student set name=?,birthday=?,score=?,email=?,tel=?,status=? where id =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"wjc25");
 
        String stringDate="2025-01-01";
        preparedStatement.setDate(2,new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse(stringDate).getTime()));
 
        preparedStatement.setDouble(3,250.5);
        preparedStatement.setString(4,"250.com");
        preparedStatement.setString(5,"1568988-456");
        preparedStatement.setInt(6,1);
        preparedStatement.setInt(7,2025);
        int count = preparedStatement.executeUpdate();
        System.out.println(count>0);
 
//        preparedStatement.close();
//        connection.close();
 
 
    }
//          删除数据
    public static void DeleteData(Connection connection) throws Exception{
        String sql="delete from student where id =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,2025);
        int count = preparedStatement.executeUpdate();
        System.out.println(count>0);
 
        preparedStatement.close();
        connection.close();
 
 
    }
 
    }
 
 
class student{
    int id;
    String name;
    java.sql.Date birthday;
    double score;
    String email;
    String tel;
    int stutus;
 
    public student(){
 
    }
 
    public student(int id,String name, java.sql.Date birthday, double score, String email, String tel, int stutus){
        this.id=id;
        this.name=name;
        this.birthday=birthday;
        this.score=score;
        this.email=email;
        this.tel=tel;
        this.stutus=stutus;
    }
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public Date getBirthday() {
        return birthday;
    }
 
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
 
    public double getScore() {
        return score;
    }
 
    public void setScore(double score) {
        this.score = score;
    }
 
    public String getEmail() {
        return email;
    }
 
    public void setEmail(String email) {
        this.email = email;
    }
 
    public String getTel() {
        return tel;
    }
 
    public void setTel(String tel) {
        this.tel = tel;
    }
 
    public int getStutus() {
        return stutus;
    }
 
    public void setStutus(int stutus) {
        this.stutus = stutus;
    }
 
    @Override
    public String toString() {
        return
                "id=" + id +"\n"+
                "name='" + name + "\n"+
                "birthday=" + birthday +"\n"+
                "score=" + score +"\n"+
                "email='" + email + "\n" +
                "tel='" + tel +  "\n" +
                "stutus=" + stutus +"\n";
    }
}
posted @   Wang_JC  阅读(19)  评论(0)  编辑  收藏  举报
相关博文：
·  Mybatis
·  mysql
·  MYSQL的Java操作器——JDBC
·  JDBC详解
·  JavaWeb--JDBC：概述，DriverManager，Connection，Statement，ResultSet，PreparedStatement、数据库连接池--2022年9月23日
阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试？测试工程师会被淘汰吗？
· 使用C#创建一个MCP客户端
Title
点击右上角即可分享
微信分享提示
SQL AI 助手