Java Database Connection - JDBC Details

Keywords: Java Database JavaSE intellij-idea

Overview of JDBC

JDBC (Java Database Connectivity) is a set of interface to connect database provided by Java language. The implementation of interface is called database driver in JDBC and is provided by major database manufacturers. The reason for this is that the user doesn't need to care about how the underlying implementation is implemented, and the implementer can't tell you what the implementation details at the bottom of my database are. The mysql database is open source, so the driver it provides contains the source code. Users who need Java programs to connect to their database products need to go to the corresponding database website to download the driver jar package. The idea behind this design of JDBC is to program interfaces, not implementations, abstraction should not depend on detail, and detail should not depend on abstraction, that is, on the inversion principle.



IDEA Import jar Package

Mode 1:

  1. The mysql-connector downloaded from the official website is decompressed directly and the driver jar package inside is copied.
  2. Create a lib directory under idea your project and paste in the copied driver jar package.
  3. Right-click Driver Jar Package --> Select as Library to successfully add the driver jar package to the project's Libraries.

Mode 2:

Select File --> Open Project Structure --> Click on the + sign to create a Java project library --> Select the jar package you want to import --> Finally click apply to apply to the project.



Six Steps in JDBC Programming

Step 1: Register Drivers

  1. First way:
    try{
    Driver driver = new com.mysql.cj.jdbc.Driver();
	DriverManager.regisertDriver(driver);
}catch(SQLException e){
    e.printStackTrace();
}

    Note: The Driver on the left belongs to the java.sql package, while the Driver on the right belongs to the com.mysql.cj.jdbc package.

  2. The second way (how classes are loaded):

    As you can see from the source code of the com.mysql.cj.jdbc.Driver class, we do not need to create a Driver object registration driver. When the Driver class loads, the registration driver method in the static code block is automatically executed. So we can write as follows:

    try{
	Class.forName("com.mysql.cj.jdbc.Driver");    
}catch(ClassNotFoundException e){
    e.printStackTrace();
}

    The advantage of this is that the program is flexible and the code is not written to death. If the program wants to change a database, it only needs to pass a string of the fully qualified name of the database, which can be saved in the configuration file and obtained from the resource binder ResourceBunlde binding configuration file. The code here demonstrates getting the database connection object later.

    Source code for the com.mysql.cj.jdbc.Driver class:

    public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

Step 2: Get the database connection object

/*
	url(uniform resource locator):Uniform Resource Locator is a method for specifying the location of information or resources on the World Wide Web of the Internet.
	
	URL What parts are included?
		Protocols: Common are http, https, ftp, and the name of the protocol connecting to the MySQL database is jdbc:mysql.
		Domain name or ip address: You can use localhost or 172.0.0.1 to connect to your local mysql database.
		Port number: mysql is 3306,http is 80,https is 443,ftp is 21.
		Resource name: The path of the resource on the server.
		Carried parameters: use? Name1=value1&name2=value2&name3=value3... This format.
		
	For example: jdbc:mysql//172.0.0.1/login?UseUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
*/

String url;
String user;
String password;
Connection connection = DriverManager.getConnection(url,user,password);

java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

Using a higher version of mysql to drive jar packages may report the above errors, but the lower version will not.

Solution:
Add after the connection string in the configuration file? serverTimezone=UTC

UTC is Unified Standard World Time

Complete connection string example: jdbc:mysql://localhost:3306/hello?serverTimezone=UTC

If you specify a time zone using gmt+8, you need to write GMT%2B8, otherwise you may report an error parsing to null

By the way, if the input Chinese is garbled, you can set it as follows:

jdbc:mysql://127.0.0.1:3306/hello?useUnicode=true&characterEncoding=UTF-8

Of course, for your safety, you can also write as follows:

jdbc:mysql://127.0.0.1:3306/hello?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC

Done.
Reference resources: https://blog.csdn.net/weixin_42652696/article/details/83864740

Step 3: Get the database operation object

//Statement objects are designed to execute sql statements
Statement statement = connection.createStatement();

Step 4: Execute SQL

String sql = "insert into login(name,psw) values('jack','123')";

//Designed to execute DML statements (insert delete update), the return value is the number of entries that affect database records
int count = statement.executeUpdate(sql);

Step 5: Processing the query result set (you can skip this step for DML statements)

//ResultSet is used to process a JDBC query result set
ResultSet resultSet = statement.executeQuery(sql);

//The next() method is used to determine if the cursor is currently executing a row with data
while(resultSet.next()){
    //When you press a subscript to fetch data, all the subscripts in JDBC start with 1, which is too lethal to write. Column names are commonly used to fetch data.
    int id = resultResult.getInt(1);
    String name = resultResult.getString(2);
    String password = resultResult.getString(3);
    System.out.println(id +" "+ name +" "+ password);
    
    //Data is taken by column name, because query statements can alias column names, so note the names in the query result set. In addition to getInt(), getString(), there are other ways to get data of a specified type.
    int id = resultResult.getInt("id");
    String name = resultResult.getString("name");
    String password = resultResult.getString("psw");
    System.out.println(id +" "+ name +" "+ password);
}

The data encapsulated in the ResultSet object is organized as follows

Step 6: Release Resources

//The resources created to maintain a connection to the database must be freed and then closed. To ensure that programs terminate because of exceptions and shut down resources normally, they are usually placed in a final statement block
resultSet.close();
statement.close();
connection.close();


Complete six-step programming example

import java.sql.*;

public class TestJDBC01 {
    public static void main(String[] args) {
        Statement statement = null;
        Connection connection = null;
        ResultSet resultSet = null;

        try {
            //Step 1: Register Drivers
            Class.forName("com.mysql.cj.jdbc.Driver");

            //Step 2: Get connected
            String url = "jdbc:mysql://127.0.0.1:3306/login?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
            String user = "root";
            String password = "admin";
            connection = DriverManager.getConnection(url,user,password);

            //Step 3: Get the database operation object
            statement = connection.createStatement();

            /*
            //Step 4: Execute SQL
            String dmlSQL = "insert into u_login(name,psw) values('jack','123')";

            //Designed to execute DML statements (insert delete update), the return value is the number of entries that affect database records
            int count = statement.executeUpdate(dmlSQL);
            System.out.println(count);
            */

            //Step 5: Processing the query result set
            String dqlSQL = "select id,name,id from u_login where id = 1";

            resultSet = statement.executeQuery(dqlSQL);

            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String psd = resultSet.getString("psw");
                System.out.println(id + " " + name + " " + psd);
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            //Step 6: Close resources
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}


How to get the required information through a configuration file

public class Test02 {
    public static void main(String[] args) {
        Statement statement = null;
        Connection connection = null;
        ResultSet resultSet = null;

        //Binding property profiles using resource binders
        ResourceBundle resourceBundle = ResourceBundle.getBundle("com/ace/test/jdbc");
        String driverFullName = resourceBundle.getString("driver");
        String url = resourceBundle.getString("url");
        String user = resourceBundle.getString("user");
        String password = resourceBundle.getString("password");

        try {
            //Register Driver
            Class.forName(driverFullName);

            //Get Connections
            connection = DriverManager.getConnection(url,user,password);

            //Get the database operation object
            statement = connection.createStatement();

            //Processing query result set
            String dqlSQL = "select id,name,psw from u_login where id = 1";

            resultSet = statement.executeQuery(dqlSQL);

            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String psd = resultSet.getString("psw");
                System.out.println(id + " " + name + " " + psd);
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            //close resource
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}


SQL Injection Phenomena

Overview of SQL Injection:

Sql injection attack is an attack by inserting malicious Sql queries or add statements into the input parameters of the application and parsing the execution on the background Sql server. It is currently one of the most common means for hackers to attack the database.

What is the root cause of SQL injection?

User-entered keywords contain sql keywords and participate in the compilation of sql statements, which distorts the original meaning of the sql statements and leads to sql injection.

SQL Injection Example

public class Test03 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Scanner scanner = new Scanner(System.in);

        try{
            System.out.println("Please enter the login username and password:");
            String loginName = scanner.next();
            String loginPassword = scanner.next();
            scanner.close();

            //Register Driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://127.0.0.1:3306/login?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
            String user = "root";
            String dbPassword = "admin";
            //Get Connections
            connection = DriverManager.getConnection(url,user,dbPassword);

            //Get the database operation object
            statement = connection.createStatement();

            //Execute sql
            String sql = "select * from u_login where name='" + loginName + "' and psw='" +loginPassword + "'";
            System.out.println(sql);
            resultSet = statement.executeQuery(sql);

            //Processing query result set
            if(resultSet.next()){
                System.out.println("Login successful!");
            }else {
                System.out.println("Login failed!");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}



PreparedStatement Solves SQL Injection Problems

SQL injection occurs because the information entered by the user contains SQL keywords that participate in the compilation of the SQL statement, causing the original meaning of the SQL statement to be distorted. Therefore, as long as the information entered by the user does not participate in the compilation of the SQL statement, the problem is solved. Even if the information entered by the user contains SQL keywords, it does not participate in the compilation and therefore does not work.

To use the information entered by the user without participating in the compilation of the SQL statement, you must use java.sql.PreparedStatement. The PreparedStatement interface inherits Satement and is a precompiled database operation object by compiling the "framework" of the SQL statement in advance and then passing the "value" to the SQL statement.

public class Test03 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Scanner scanner = new Scanner(System.in);

        try{
            System.out.println("Please enter the login username and password:");
            String loginName = scanner.next();
            String loginPassword = scanner.next();
            scanner.close();

            //Register Driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://127.0.0.1:3306/login?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
            String user = "root";
            String dbPassword = "admin";
            //Get Connections
            connection = DriverManager.getConnection(url,user,dbPassword);

            //Get the database operation object, send the sql statement to the DBMS for precompilation, one? Represents a placeholder that will be used to receive a value in the future. Note that placeholders do not need to be enclosed in single quotes.
            String sql = "select * from u_login where name = ? and psw = ?";
            preparedStatement = connection.prepareStatement(sql);

            //Here? Pass "Value" (all JDBC subscripts start at 0)
            preparedStatement.setString(1,loginName);
            preparedStatement.setString(2,loginPassword);

            //Executing sql eliminates the need to pass in sql statements.
            resultSet = preparedStatement.executeQuery();

            //Processing query result set
            if(resultSet.next()){
                System.out.println("Login successful!");
            }else {
                System.out.println("Login failed!");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(preparedStatement != null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}


Statement versus PreredStatement

  1. Statement did not solve the SQL injection problem, while PreparedStatement solved the SQL injection problem.
  2. Statement is DBMS compilation executed once, while PreparedStatement is DBMS compilation executed once, N times. PreparedStatement is more efficient.
  3. PreparedStatement performs type security checks during the compilation phase.

Scenarios for using Statement s

Since Statements cause SQL injection and are less efficient and secure than PreparedStatement, why not discard the use of Statement and use PreparedStatement entirely?

Since Statement still exists, it certainly makes sense. Statement results in SQL injection, where the user's input contains a misinterpreted SQL keyword. But programmers often need to take advantage of this feature and add some SQL keywords to fulfill our needs, in which case PreparedStatement can't. To prevent users from injecting SQL, we may not provide an injection opportunity. For example, for the display of something, users often need it to be displayed in ascending and descending order by keywords. It can only provide users with some operational buttons, users can only click and not enter. For different choices, programmers can stitch different sort keywords to achieve this requirement. In this case, only Statement can be used.



JDBC Transaction Mechanism

Transactions in JDBC are committed automatically, which means that a DML statement is executed and committed once. But in real business, N DML statements are usually combined to complete. These DML statements must either succeed or fail simultaneously.

JDBC transaction example (account transfer)

public class TestJDBCTransaction {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try{
            //Register Driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            //Get Connections
            String url = "jdbc:mysql://127.0.0.1:3306/login?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
            String user = "root";
            String password = "admin";
            connection = DriverManager.getConnection(url,user,password);

            //Change JDBC's automatic commit mechanism to manual commit. Default to true, auto-submit.
            connection.setAutoCommit(false);    //Open Transaction

            //Get the database operation object
            String sql = "update t_account set balance = ? where number = ?";
            preparedStatement = connection.prepareStatement(sql);


            //Execute SQL
            //The balance of account 123 is still 10,000.00 yuan, which is transferred to account 456.
            preparedStatement.setDouble(1,0);
            preparedStatement.setInt(2,123);
            int count = preparedStatement.executeUpdate();

            //The balance of account 456 still has 5,000 yuan and receives a transfer of 10,000 yuan for account 123.
            preparedStatement.setDouble(1,15000);
            preparedStatement.setInt(2,456);
            count += preparedStatement.executeUpdate();

            System.out.println(count==2?"Transfer succeeded":"Transfer failure");

            //The program can go to this to show that the above program has no exceptions, the transaction ends, and the transaction is committed manually.
            connection.commit();    //Submit Transaction
        }catch (Exception e){
            if(connection != null){
                //When the program comes here, it says an exception has occurred and the transaction needs to be rolled back.
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        }finally {
            if(preparedStatement != null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Row-level locks and pessimistic locks for transactions

Encapsulation of JDBC Tool Classes

encapsulation

import java.sql.*;

public class JDBCUtil {
    private JDBCUtil(){}

    //Static code block, executed only once when a class is loaded, calling a static method triggers the loading of the class, and completing database-driven registration when the JDBC loads
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/login?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
        String user = "root";
        String password = "admin";
        return DriverManager.getConnection(url,user,password);
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Use

public class TestJDBCUtil {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();

            String sql = "select id,name,psw from u_login where name=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"jack");

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                System.out.println(resultSet.getString("id") + " "
                + resultSet.getString("name") + " "
                + resultSet.getString("psw"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(connection,preparedStatement,resultSet);
        }
    }
}

Posted by jigen7 on Thu, 11 Nov 2021 08:30:54 -0800

Hot Keywords

©2024 Programmer Group