JDBC学习

1.认识JDBC

JDBC: Java Database Connect Java连接数据库

图片.png

需要jar包支持：

• java.sql
• javax.sql
• mysql-connector-java

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.26/mysql-connector-java-8.0.26.jar

2. 准备测试数据库

2.1 创建数据库

CREATE DATABASE jdbc;

2.2 创建数据表

CREATE TABLE IF NOT EXISTS `user`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `username` VARCHAR(100) NOT NULL,
   `password` VARCHAR(40) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.3 插入数据

INSERT INTO user(username,password )
VALUES( "张三", "123456"),
( "李四", "123456"),
( "张三疯", "123456");

3. 编写JDBC

• JDBC_statement_CRUD

package com.zzqsmile;

import java.sql.*;

public class JDBC_statement_CRUD {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2. 配置信息
        String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false";
        String username="root";
        String password="root";

        //3. 连接数据库,Connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //4. 向数据库发送sql的对象Statement&&PreparedStatement,用来CRUD
        Statement statement = connection.createStatement();
        //编写查询sql   --查
        String sql="select * from user;";
        //执行
        ResultSet resultSet = statement.executeQuery(sql);
        //打印信息
        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("username="+resultSet.getObject("username"));
            System.out.println("password="+resultSet.getObject("password"));
            System.out.println("=======================");
            System.out.println();
        }

        //编写插入sql   --增
        String sql1="insert into user(username,password) values('刘五','111111')";
        //执行
        int i = statement.executeUpdate(sql1);
        if (i>0){
            System.out.println("插入成功！");
            System.out.println();
        }

        //编写修改sql   --改
        String sql2="update user set `password`='123123' where `username`='刘五'";
        //执行
        int i1 = statement.executeUpdate(sql2);
        if (i1>0){
            System.out.println("修改成功！");
            System.out.println();
        }

        //编写删除sql   --删
        String sql3="delete from user where id = 1";
        //执行
        int i2 = statement.executeUpdate(sql3);
        if (i2>0){
            System.out.println("删除成功！");
            System.out.println();
        }


        //5. 关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

- JDBC_PreparedStatement_CRUD

预编译CURD

package com.zzqsmile;

import java.sql.*;

public class JDBC_PreparedStatement_CRUD {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2. 配置信息
        String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false";
        String username="root";
        String password="root";

        //3. 连接数据库,Connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //4. 编写参数化SQL(带?的SQL语句)
        //编写查询sql   --查
        String sql="select * from user where username = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //赋值
        preparedStatement.setString(1,"李四");

        //执行
        ResultSet resultSet = preparedStatement.executeQuery();
        //打印信息
        while (resultSet.next()){
            System.out.println("===========查询结果============");
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("username="+resultSet.getObject("username"));
            System.out.println("password="+resultSet.getObject("password"));
            System.out.println();
        }

        //编写插入sql   --增
        String sql1="insert into user(username,password) values(?,?)";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);

        //赋值
        preparedStatement1.setString(1,"李四2");
        preparedStatement1.setString(2,"123321");
        //执行
        int i1 = preparedStatement1.executeUpdate();
        if (i1>0){
            System.out.println("插入成功！");
            System.out.println();
        }

        //编写修改sql   --改
        String sql2="update user set `password`=? where `username`='刘五'";
        PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);

        //赋值
        preparedStatement2.setString(1,"222222");
        //执行
        int i2 = preparedStatement2.executeUpdate();
        if (i2>0){
            System.out.println("修改成功！");
            System.out.println();
        }

        //编写删除sql   --删
        String sql3="delete from user where id = ?";
        PreparedStatement preparedStatement3 = connection.prepareStatement(sql3);

        //赋值
        preparedStatement3.setString(1,"2");
        //执行
        int i3 = preparedStatement3.executeUpdate();
        if (i3>0){
            System.out.println("删除成功！");
            System.out.println();
        }

        //5. 关闭连接
        resultSet.close();
        preparedStatement.close();
        preparedStatement1.close();
        preparedStatement2.close();
        preparedStatement3.close();
        connection.close();
    }
}

---

3. 提取工具类JDBC_utils

• src目录下创建db.properties文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false
username=root
password=root

JDBC_utils:

package com.zzqsmile.lesson2.utils;

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

public class JDBC_utils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream resourceAsStream = JDBC_utils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1.驱动只用加载一次
            Class.forName(driver);

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

    // 2. 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);

    }

    //3. 释放资源，关闭链接`close(Connection conn,Statement stmt,ResultSet rs)`
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) { // 防止空指针
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 4.重载关闭方法`close(Connection conn, Statement stmt)`
    public static void close(Connection conn, Statement stmt) {
        close(conn, stmt, null);
    }
}

• 使用JDBC_utils改进编写Statement的CURD:

package com.zzqsmile.lesson2;

import com.zzqsmile.lesson2.utils.JDBC_utils;

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

public class Test_Statement {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JDBC_utils.getConnection();  //获得数据库连接
            st = conn.createStatement();    //获得SQL执行对象

            //增
            String sql = "insert into user(username,password) values('admin1','333333')";
            int i = st.executeUpdate(sql);

            if (i>0){
                System.out.println("插入成功！");
                System.out.println();
            }

            //删
            String sql1 = "delete from user where id = '10'";
            int i1 = st.executeUpdate(sql1);

            if (i1>0){
                System.out.println("删除成功！");
                System.out.println();
            }

            //改
            String sql2 = "update user set password = '135790' where username = 'admin1'";
            int i2 = st.executeUpdate(sql2);

            if (i2>0){
                System.out.println("修改成功！");
                System.out.println();
            }

            //查
            String sql3 = "select * from user where username = 'admin1'";
            ResultSet resultSet = st.executeQuery(sql3);

            if (resultSet.next()){
                System.out.println("查询成功！");
                System.out.println("id="+resultSet.getObject("id"));
                System.out.println("username="+resultSet.getObject("username"));
                System.out.println("password="+resultSet.getObject("password"));
                System.out.println();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_utils.close(conn,st);
        }
    }
}

• 使用JDBC_utils改进编写PreparedStatement的CURD:

package com.zzqsmile.lesson2;

import com.zzqsmile.lesson2.utils.JDBC_utils;

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

public class Test_PreparedStatement {
    public static void main(String[] args) {
        try {
            //获得数据库连接
            Connection connection = JDBC_utils.getConnection();

            //编写SQL     --增加
            String sql1 = "insert into user(username,password) values(?,?)";

            //获得SQL预编译执行对象
            PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);

            //赋值
            preparedStatement1.setString(1,"admin");
            preparedStatement1.setString(2,"admin");

            //执行
            int i1 = preparedStatement1.executeUpdate();
            if (i1>0){
                System.out.println("添加成功");
                System.out.println();
            }

            //编写SQL     --删除
            String sql2 = "delete from user where id = ?";

            //获得SQL预编译执行对象
            PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);

            //赋值
            preparedStatement2.setString(1,"5");

            //执行
            int i2 = preparedStatement2.executeUpdate();
            if (i2>0){
                System.out.println("删除成功");
                System.out.println();
            }

            //编写SQL     --更新
            String sql3 = "update user set password = '123321' where username = ?";

            //获得SQL预编译执行对象
            PreparedStatement preparedStatement3 = connection.prepareStatement(sql3);

            //赋值
            preparedStatement3.setString(1,"admin");

            //执行
            int i3 = preparedStatement3.executeUpdate();
            if (i3>0){
                System.out.println("修改成功");
                System.out.println();
            }

            //编写SQL     --查询
            String sql = "select * from user where username = ?";

            //获得SQL预编译执行对象
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            //赋值
            preparedStatement.setString(1,"admin");

            //执行
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println("===========查询结果============");
                System.out.println("id="+resultSet.getObject("id"));
                System.out.println("username="+resultSet.getObject("username"));
                System.out.println("password="+resultSet.getObject("password"));
                System.out.println();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}