JDBC study notes (1) Overview of JDBC, obtaining database connections, using Statement and PreparedStatement in JDBC to implement additions, deletions, and changes

JDBC study notes (1)

Earlier we learned about MySQL. In fact, the MySQL database management system is a server. For example, command lines and visualization tools are all clients. You can interact with the server by providing the server's IP, port number, and user name and password. .

Insert picture description here

1. JDBC overview

In JAVA, database access technology can be divided into the following categories:

  • JDBC directly access the database
  • JDO technology (Java Data Object) : JDBC is only oriented to relational databases (RDBMS), JDO is more versatile and provides storage functions to the bottom of any data, such as relational databases, files, XML, and object databases (ODBMS), etc., making applications portable Stronger sex.
  • Third-party O/R tools, such as Mybatis, etc.

JDBC is the cornerstone of Java to access the database, Mybatis, etc. just better encapsulate JDBC.


1.1 What is JDBC

JDBC (Java Database Connectivity): Java technology to connect to the database.

JDBC usually refers to the unified standard established by SUN for how to connect and operate DBMS software for major database vendors' Java programs, that is, the public interface. This public interface is provided by major database vendors with implementation classes, and these implementation classes constitute the database driver.

When Java programmers write Java code, they only need to program for the interface, and add the driver implementation class to the project at runtime. Java programmers only need to learn the JDBC public interface provided by SUN.

The role of JDBC can be explained in depth in the following figure.

  • If there is no JDBC, then the Java program accesses the database as shown in the figure below. Different DBMS software is developed by different companies. If the Java program wants to connect and operate the DBMS, not only SQL statements are enough, but other APIs (classes, methods, etc.) may also be supported.
Insert picture description here
  • JDBC provides a unified way for accessing different databases. The goal of JDBC is to enable Java programmers to use JDBC to connect to any database system that provides a JDBC driver, so that programmers do not need to have a certain understanding of the characteristics of a particular database system, which greatly simplifies the development process.
Insert picture description here

Summary: JDBC is a set of interface APIs provided by Sun company for database operations. Java programmers only need to program for this set of interfaces. Different database vendors need to provide different implementation classes for this set of interfaces. Different implementation classes are the driver classes for different databases.


1.2 JDBC API

JDBC API is a series of interfaces, which stipulates and regulates the connection between the application and the database, executes SQL statements, and waits until the result is returned. The declaration is in the java.sql and javax.sql packages.

Insert picture description here

1.3 JDBC programming steps

  • The steps of the JDBC program to access the database are shown in the figure below.
Insert picture description here

2. Get database connection

The steps to obtain the database connection can be implemented with the following steps.

(1) Introduce the JDBC driver

The driver is provided for download by the database provider. MySQL driver download address: http://dev.mysql.com/downloads/.

How to add a database-driven jar to the Java Project project application:

(1) First copy mysql-connector-java-5.1.36-bin.jar to the libs directory in the project.

Insert picture description here

(2) Add the jar package to the class path of the project.

Insert picture description here
Insert picture description here
Insert picture description here

(2) Load and register the driver

Load and register the driver:
load the driver and load the driver class into the memory.
Register the driver and hand over the objects of the driver class to DriverManager for management, which will be used to create connections later.

1.Class.forName()

Call the static method forName() of the Class class and pass it the class name of the JDBC driver to be loaded.

Insert picture description here

(3) Get database connection

A connection to the database can be established through the DriverManager class Connection:

DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers.

Method to establish a connection to the database through the DriverManager class Connection
public static Connection getConnection(String url)
public static Connection getConnection(String url,String user, String password), this method is the most commonly used
public static Connection getConnection(String url,Properties info) where Properties info should usually include at least "user" and "password" properties

The JDBC URL is used to identify a registered driver, and the driver manager selects the correct driver through this URL to establish a connection to the data.

jdbc:<子协议>:<子名称>
协议:JDBC URL 中的协议总是jdbc
子协议:子协议用来标识一个数据库驱动程序
子名称:一个标识数据库的方法。用子名称的目的是为了定位数据库提供足够的信息

E.g:

Insert picture description here
MySQL的连接URL编写方式:
jdbc:mysql://主机名称:mysql服务端口号/数据库名称?参数=值&参数=值
例如:jdbc:mysql://localhost:3306/testdb
Insert picture description here

(4) Operate or access the database

The database connection is used to send commands and SQL statements to the database server and accept the results returned by the database. In fact, a database connection is a Socket connection.

Three interfaces in the java.sql package define different ways of calling the database:

  • Statement: An object used to execute a static SQL statement and return the result it produces.
  • PreparedStatement: The SQL statement is precompiled and stored in an object, and then this object can be used to execute the statement efficiently multiple times.
  • CallableStatement: used to execute SQL stored procedures.

Statement

Create the object by calling the createStatement() method of the Connection object.

This object is used to execute static SQL statements and return the execution results.

The following methods are defined in the Statement interface to execute SQL statements:

int excuteUpdate(String sql):执行更新操作insert、update、delete

ResultSet excuteQuery(String sql):执行查询操作select

ResultSet

Create the object by calling the excuteQuery() method of the Statement object.

The ResultSet object maintains a cursor pointing to the current data row. Initially, the cursor is before the first row and can be moved to the next row through the next() method of the ResultSet object.

Common methods of ResultSet interface
boolean next(): move to the next line
get***(String columnLabel): columnLabel uses the list label specified by the SQL AS clause. If the SQL AS clause is not specified, the label is the column name
get***(int index): index starts from 1

(5) Release resources

Connection, Statement, and ResultSet are all connection resources between the application and the database server. They must be closed after use, and can be closed in finally.


3. JDBC example code for adding, deleting, modifying, and checking operations

Example code for adding data operations using JDBC:

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

public class JDBCTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        /**
         * 1.使用JDBC向mysql数据库中添加记录
         *
         */
        //1.通过反射,加载与注册驱动类
         Class.forName("com.mysql.jdbc.Driver");
         //2.获取数据库连接
         String url="jdbc:mysql://localhost:3306/school";
         Connection connection = DriverManager.getConnection(url, "root", "root");

        //3.创建Statement对象
        Statement statement = connection.createStatement();

        //4.编写sql语句
        String sql="insert books values(7,'SpringBoot学习',230)";

        //5.执行sql语句
        int len = statement.executeUpdate(sql);//insert,update以及delete都用这个方法

        System.out.println(len>0?"添加成功":"添加失败");

        //6.关闭资源
        statement.close();
        connection.close();


    }
}

The result after running is:

Insert picture description here


Insert picture description here

Example code for modifying data operations using JDBC:

        /**
         * 2.使用JDBC将mysql数据库中的数据进行修改
         *
         *
         */
        //1.通过反射,加载与注册驱动类
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取数据库连接
        String url="jdbc:mysql://localhost:3306/school";
        Connection connection = DriverManager.getConnection(url, "root", "root");

        //3.创建Statement对象
        Statement statement = connection.createStatement();

        //4.编写sql语句
        String sql="update books set bname='详述SpringMVC'  where bid=6 ";

        //5.执行sql语句
        int len = statement.executeUpdate(sql);//insert,update以及delete都用这个方法

        System.out.println(len>0?"修改成功":"修改失败");

        //6.关闭资源
        statement.close();
        connection.close();

The result after running is:

Insert picture description here
Insert picture description here

Example code for deleting data using JDBC:

        /***
         * 3.使用JDBC将mysql数据库中的数据进行删除操作
         */

        //1.通过反射,加载与注册驱动类
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取数据库连接
        String url="jdbc:mysql://localhost:3306/school";
        Connection connection = DriverManager.getConnection(url, "root", "root");

        //3.创建Statement对象
        Statement statement = connection.createStatement();

        //4.编写sql语句
        String sql="delete  from books   where bid=2 ";

        //5.执行sql语句
        int len = statement.executeUpdate(sql);//insert,update以及delete都用这个方法

        System.out.println(len>0?"删除成功":"删除失败");

        //6.关闭资源
        statement.close();
        connection.close();;

The result after running is:

Insert picture description here


Insert picture description here

Example code for query data operation using JDBC:

        /***
         * 4.使用JDBC将mysql数据库中的数据进行查询操作
         */

        //1.通过反射,加载与注册驱动类
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取数据库连接
        String url="jdbc:mysql://localhost:3306/school";
        Connection connection = DriverManager.getConnection(url, "root", "root");

        //3.创建Statement对象
        Statement statement = connection.createStatement();

        //4.编写sql语句
        String sql="select * from  books ";

        //5.执行sql语句
        ResultSet resultSet = statement.executeQuery(sql);//执行sql查询语言,使用executeQuery()

        //6.遍历resultSet
        while(resultSet.next()){
            int bid = resultSet.getInt("bid");
            String bname = resultSet.getString("bname");
            double price = resultSet.getDouble("price");

            System.out.println(bid+"=="+bname+"=="+price);
        }

        //7.关闭资源
        statement.close();
        connection.close();


    }
}

The result after running is;

Insert picture description here

4.PreparedStatement

The PreparedStatement interface is a sub-interface of Statement, which represents a pre-compiled SQL statement.

4.1 Insufficiency of Statement

(1)SQL splicing

        /**
         * 1.Statement:SQL拼接
         *
         */
        Scanner input = new Scanner(System.in);
        System.out.println("请输入书籍的名称:");
        String bname = input.next();

        System.out.println("请输入书籍的价格:");
        double price = input.nextDouble();

        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.连接数据库
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/school","root","root");
        //3.创建Statement对象
        Statement statement = connection.createStatement();
        //4.编写sql语句
        String sql="insert into books values(2,'" + bname+"'," + price + ")";
        //5.执行sql语句b
        int len = statement.executeUpdate(sql);
        System.out.println(len>0?"添加成功":"添加失败");
        //6.关闭资源
        statement.close();
        connection.close();

The result after running is:

Insert picture description here


Insert picture description here

(2) SQL injection

SQL injection is the use of certain systems that do not fully check the data input by the user, but inject illegal SQL statements into the user input data, thereby using the system's SQL engine to complete malicious behavior. For Java, to prevent SQL injection, just use PreparedStatement instead of Statement.


        /**
         * 2.Statement:SQL注入
         */

        Scanner input = new Scanner(System.in);
        System.out.println("请输入书籍的名称:");
        String bname = input.next();



        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.连接数据库
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/school","root","root");
        //3.创建Statement对象
        Statement statement = connection.createStatement();
        //4.编写sql语句
        //数据库原理  'or '1'='1
        String sql="select  bid,bname,price  from books  where bname='"+bname+"'";
        //5.执行sql语句b
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            double price = resultSet.getDouble(3);

            System.out.println(id+"\t"+name+"\t"+price);
        }
        //6.关闭资源
        statement.close();
        connection.close();

The result after running is:

Insert picture description here


Insert picture description here

(3) Processing Blob type data

BLOB (binary large object), binary large object, BLOB is a field type used by the database to store binary files.

You must use PreparedStatement to insert BlOB type numbers, because BLOB type data cannot be spliced ​​using strings.

Insert picture description here

4.2 Overview of PreparedStatement

Get the PreparedStatement object by calling the PreparedStatement(String sql) method of the Connection object.

The PreparedStatement interface is a sub-interface of Statement, which represents a pre-compiled SQL statement.

The parameters in the SQL statement represented by the PreparedStatement object are represented by question marks (?). Call the set() method of the PreparedStatement object to set these parameters.

ResultSet executeQuery() executes the query and returns the Result object generated by the query.

int executeUpdate(): execute update, including addition, deletion, and modification.

Use PreparedStatement to solve splicing in SQL statements

public static void add() throws ClassNotFoundException, SQLException {
         Scanner input = new Scanner(System.in);
         System.out.println("请输入书籍的编号:");
         int bid = input.nextInt();

         System.out.println("请输入书籍的名称:");
         String bname = input.next();

         System.out.println("请输入书籍的价格:");
         double price = input.nextDouble();

         //1.注册驱动
         Class.forName("com.mysql.jdbc.Driver");
         //2.连接数据库
         Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/school","root","root");

         //3.编写带?的sql语句
         String sql="insert into  books values(?,?,?)";

         //4.准备一个PreparedStatement:预编译sql
         PreparedStatement preparedStatement = connection.prepareStatement(sql);

         //5.把?用具体的值进行代替
         preparedStatement.setObject(1,bid);
         preparedStatement.setObject(2,bname);
         preparedStatement.setObject(3,price);

         //6.执行sql语句
         int len = preparedStatement.executeUpdate();
         System.out.println(len>0?"添加成功":"添加成功");

         //7.释放资源
         preparedStatement.close();
         connection.close();

     }

The result after execution is:

Insert picture description here
Insert picture description here

Use PreparedStatement to solve SQL injection phenomenon

  public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //从键盘输入书籍的名称,来查询书籍的信息
        Scanner input = new Scanner(System.in);

        System.out.println("请输入书籍的名称:");
        String bname = input.nextLine();//输入:计算机网络组成原理' or '1'='1
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "root");

        //3.编写sql语句
        String sql = "select * from books where bname = ?";

        //4.创建PreparedStatement
        PreparedStatement pst = connection.prepareStatement(sql);

        //插入一步,设置?号
        pst.setString(1,bname);

        //5.执行sql
        ResultSet resultSet = pst.executeQuery();

        //6.遍历resultSet
        while (resultSet.next()) {
            for (int i = 1; i <= 3; i++) {
                System.out.print(resultSet.getObject(i) + "\t");
            }
            System.out.println();
        }

        //7.关闭资源
        pst.close();
        connection.close();
        input.close();
    }

The result after running is:

Insert picture description here


Insert picture description here

Use PreparedStatement to handle binary data types such as blobs

public static void main(String[] args) throws ClassNotFoundException, SQLException, FileNotFoundException {
        //往school库的user表中添加一条记录
        //1.注册驱动
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");

        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "root");

        //3.编写sql语句
        String sql="insert into user values(3,?,?,?)";//避免拼接blob

        //4.创建PreparedStatement对象
        PreparedStatement pst = connection.prepareStatement(sql);

        //5.设置?号
        pst.setString(1,"chenliu");
        pst.setString(2,"12345678");
        pst.setBlob(3, new FileInputStream("E:\\xingye.jpg") );

        //6.执行sql
        int len = pst.executeUpdate();
        System.out.println(len>0?"添加成功":"添加失败");


        //7.关闭流
        pst.close();
        connection.close();

    }

The result after running is:

Insert picture description here


Insert picture description here

5.Comparison between PreparedStatement and Statement

  • In terms of code readability and maintainability: SQL splicing of Statement is a difficult problem.
  • PreparedStatement can handle Blob type data.
  • PreparedStatement can prevent SQL injection.
  • PreparedStatement can maximize performance.

to sum up

In this section, obtain database connection and use Statement and PreparedStatement in JDBC to implement addition, deletion, modification and checking. These basic operations need to be mastered, and the second thing to pay attention to is the comparison between PreparedStatement and Statement performance.

Insert picture description here