jdbc 1.1 what is jdbc,JDBC tutorial, use jdbc to connect to the database for addition, deletion, modification and query. PreparedStatement solves sql injection.

1. Specific contents

1.1 concept

JDBC (Java DataBase Connectivity) is a java database connection. In other words, it uses the Java language to operate the database. It turns out that we operate the database by using SQL statements on the console, and JDBC sends SQL statements to the database in Java language. JDBC (Java Data Base Connectivity) is a Java API for executing SQL statements. It can provide unified access to a variety of relational databases. It is composed of a group of classes and interfaces written in Java language. JDBC provides a benchmark by which more advanced tools and interfaces can be built to enable database developers to write database applications. JDBC is also a trade name. JDBC shields the operation differences of different underlying databases, so that developers can operate different databases through a unified Java API without considering the differences in the implementation of the underlying database. Therefore, although there are many kinds of underlying databases and their drivers, JDBC remains the same

1.2 JDBC API

Provided by sun company, the content is the interfaces and classes for programmers to call, which are integrated in java.sql and javax.sql packages. The JDBC API can do three things: establish a connection with the database, execute SQL statements, and process results.

  1. DriverManager class
    Jdbc Driver manager is a factory implementation class, which uses the factory method pattern. It is the management layer of JDBC and acts between users and drivers. DriverMangerm can register and delete the loaded Driver, obtain the Driver conforming to the url protocol according to the given url, or establish a connection for database interaction. It has two functions: 1) register Driver: let JDBC know which Driver to use. All Driver classes must contain a static part. This static part is registered by the DriverManager class when the instance is loaded. Under normal circumstances, the user will not directly call the DriverManager.regiserDriver method, but will be called automatically by the Driver when loading the Driver. Register Driver:
    Class.forName(“com.mysql.jdbc.Driver”); DriverManager.registerDriver(new com.mysql.jdbc.Driver())System.setProperty(“jdbc.drivers”,”com.mysql.jdbc.Driver”); 2) Get Connection: it tracks available drivers and establishes a Connection between the database and the corresponding driver.

  2. Dirver interface
    Is the interface of the driver object, pointing to the specific database driver object

  3. connection interface

  4. Statement interface

  5. ResultSet interface (result set)

  6. ResultSetMetaData object

An object that can be used to get information about the types and properties of columns in a ResultSet object
Get ResultSetMetaData object:
getMetaData() method of ResultSet object;
Common methods of ResultSetMetaData object:
int getColumnCount(): get the number of columns in this query
String getColumnName(int column): get the column name of the specified column in this query.
String getColumnTypeName(int column): retrieves the database specific type name of the specified column.
int getColumnDisplaySize(int column): indicates the maximum standard width of the specified column, in characters.
String getTableName(int column): gets the table name of the specified column

Structure for storing result set: header column name, data type, column quantity, no data.

// 4 get the station object and execute sql
Statement stm=conn.createStatement();
ResultSet rs=stm.executeQuery(sql);//ResultSetMetaData: store ResultSetMetaData metadata = rs.getmetadata()// 5. Treatment results:
while(rs.next()){//Judge whether there is a next line and point to the convenience line   
 for(int i=1;i<=metaData.getColumnCount();i++){//Traverse System.out.print(metaData.getColumnName(i)+":"+rs.getObject(i)+"\t");   
  }    
  System.out.println();
  }
  1. PreparedStatement
    The PreparedStatement interface inherits the Statement interface. PreparedStatement is more flexible and efficient than ordinary Statement objects. Solve SQL injection (never use Statement when logging in). Differences between using PrepareStatement objects and Statement objects
    1.Statement can be created first, and then the sql statement can be written to. Pass in sql during execution
    2. When creating a Preparedstatement, you must pass in an sql statement, because it must first be transported to the database for precompiling
    3. The parameters in the statement must be set before the Preparedstatement is executed. (the pre processed sql statement has placeholders. The parameter value needs to be specified before execution. It can be executed directly during execution without passing in sql). The PrepareStatement has set the sql statement and corresponding parameters before execution. The execution method does not need parameters

2 JDBC Driver

Java DataBase Connection: use java to connect to the database. Direct database access by jdbc driver advantages: 100% Java, fast and cross platform disadvantages: to access different databases, you need to download special JDBC drivers, different databases have to download jar packages, and the same database also needs different jar (different versions) versions, which are incompatible

2.1 steps of JDBC accessing database

(1) Driver for loading database: DriverManager.registerdriver (driver)_ Registerdriver (driver driver) class. Forname (package name. Class name) in jar drivemanager_ Write a static code in the driver class. Just load this (2) create a connection with the database. getConnection(String url, String user, String password) in the DriverManager (3) get the Statemnt object: stm=conn
(4) Write sql statements: Navicat
(5) Execute the executeQuery(String sql) in the sql Statement
(6) Processing results
(7) Free resources (close connections): the number of connections to the database is limited.

public static void main(String[] args){

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/casemanage?
            useUnicode=true&characterEncoding=utf-8&useSSL=false","root","Database password");
            Statement statement = connection.createStatement();
            int i = statement.executeUpdate("insert sc value(2008,2,3)");
            System.out.println(i);
            statement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Execute query:

    public static void main(String[] args){

        try {
            Class.forName("com.mysql.jdbc.Driver");
            //2 connect to the database
            Connection conn = DriverManager.getConnection("jdbc:mysql://Localhost: 3306 / casemanage? Useunicode = true & characterencoding = UTF-8 & usessl = false "," root "," database password ");
            //3statement
            Statement statement = conn.createStatement();
            //4sql
            String sql = "select * from sc";
            //5 execute sql
            ResultSet resultSet = statement.executeQuery(sql);
            //Returns the number of rows affected
            //6 return results

                //Gets the total number of columns in the query
            ResultSetMetaData metaData = resultSet.getMetaData(); //Get object
            int columnCount = metaData.getColumnCount();//Total number of columns

            System.out.println(metaData.getCatalogName(1)+"\n"); // Get database name
            while(resultSet.next()){
//                int anInt = resultSet.getInt(1); / / data in the first column

                for (int i = 1; i <= columnCount; i++) {
                    System.out.print(metaData.getColumnName(i)+ ":"); // Query the column name of this column
                    System.out.print("\t" + resultSet.getObject(i)+ "\t\t\t");
                }
                System.out.println("");
            }
            //7 release connection
            statement.close();
            conn.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

PreparedStatement solves sql injection

public static void main(String[] args) throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/casemanage?
        useUnicode=true&characterEncoding=utf-8&UseSSL=false", "root", "Database password");
        PreparedStatement prepared = conn.prepareStatement("select * from sc where s_no = ?");
        prepared.setInt(1,2002);
        ResultSet resultSet = prepared.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();//create object
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()){
            for (int i = 1; i <= columnCount ; i++) {
                System.out.print(resultSet.getObject(i)+"\t");
            }
            System.out.println("");
        }
    }

Return primary key

 public static void main(String[] args){

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/casemanage?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "1214521");
            PreparedStatement prepared = connection.prepareStatement("insert  stu(name) value (?)",Statement.RETURN_GENERATED_KEYS);
            prepared.setString(1,"Fei Zhang");
            int i1 = prepared.executeUpdate();
            
            System.out.println("Number of rows affected"+i1); //Returns the number of rows affected
            ResultSet resultSet = prepared.getGeneratedKeys();
            while (resultSet.next()){
                    System.out.print("New primary key"+resultSet.getObject(1)+"\t");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Tags: Java Database JDBC SQL

Posted on Sun, 05 Dec 2021 00:40:10 -0500 by empnorton