JDBC: You always have to learn to connect to a database!

(1) Getting started with JDBC

Simple explanation: Operate the database by executing sql statements in the Java language

To operate different databases through Java, specific APIs should be executed depending on the database. For simplification, Sun defined a set of APIs for all relational databases, JDBC, which only provides interfaces, and the implementation is handed over to the database manufacturer, and we are the developers.We only need JDBC-based operations for data databases

(2) Simple use of JDBC

We simply use JDBC to query the data in the database and output it to the console

For a quick demonstration, let's create a very simple table

CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), score DOUBLE(4,1) ); INSERT student(id,NAME,score) VALUES (1,'Zhang San',98); INSERT student(id,NAME,score) VALUES (2,'Li Si',96); INSERT student(id,NAME,score) VALUES (3,'King Five',100);

We write a corresponding student class based on the information in the database

public class Student { private int id; private String name; private double score; //Omit construction, Get, Set, toString methods ...... }

Here is a simple use of the JDBC query function

package cn.ideal.jdbc; import cn.ideal.domain.Student; import java.sql.*; public class JdbcDemo { public static void main(String[] args) { //Import Database Driven Package Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { //Load Driver Class.forName("com.mysql.jdbc.Driver"); //Get the connection object to the database connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "root99"); //Define sql statement String sql = "SELECT * FROM student"; //Gets the object statement that executes the sql statement statement = connection.createStatement(); //Execute sql statement to get result set resultSet = statement.executeQuery(sql); //Traversing through the resulting set while (resultSet.next()) { int id = resultSet.getInt(1); String name = resultSet.getString(2); Double score = resultSet.getDouble(3); Student student = new Student(); student.setId(id); student.setName(name); student.setScore(score); System.out.println(student.toString()); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { //Release resources, then call first release 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(); } } } } } //Run Results Student Student Student

Let's begin by explaining in detail the objects used above

(3) JDBC Object Details

(1) DriverManager

A: Load Driver-->Register Driver

First of all, we need to know what the two words load driver and register driver mean. When we first come into contact, some people will always have friends who use Class.forName(com.mysql.jdbc.Driver) as a registered database-driven statement, but otherwise, it loads the class represented by the parameter into memory and initializes it, the same asStatic variables are also initialized and static code blocks are executed

• Confusion: Can you use the loadClass() method in the ClassLoader class?

  • No, this method is loaded but not initialized

//Class Class Source Selection-jdk8 * A call to {@code forName("X")} causes the class named * {@code X} to be initialized.

As for initialization, let's just go back to our main line

Why not initialize a class and you can't choose one?

This is because the real registration driver (telling the program which database to use to drive the jar) is:

static void registerDriver(Driver driver)

We found the class Driver in the jar package and looked at its source code

//Static code blocks in the com.mysql.jdbc.Driver class static { try { DriverManager.registerDriver(new Driver()); } catch (SQLException var1) { throw new RuntimeException("Can't register driver!"); } }

After the class is loaded, the static method DriverManager in the class is executed to register the driver

We may have seen the code in the following 2, but the driver is actually loaded twice because it executes

The new com.mysql.jdbc.Driver() has already loaded the driver once

//1. Recommendation Class.forName("com.mysql.jdbc.Driver"); //2. Not recommended DriverManager.registerDriver(new com.mysql.jdbc.Driver())

So why bother?Isn't it good to write new com.mysql.jdbc.Driver() directly like this?

But we still choose to reject!Why?

If we do this, we rely heavily on the jar package. If we face multiple projects or need to modify the database, we need to modify the code and recompile it, but if we use Class class loading, it ensures that the registration-driven methods contained in the static code block will be executed and will refer toNumbers become strings, so we can then handle the problem more flexibly by modifying the contents of the configuration file''+ adding jar packages, without having to recompile!

Note: Driver jar packages after mysql5 can omit this step of registering drivers, because check the META-INF/services/java.sql.Driver file in the jar package

com.mysql.jdbc.Driver com.mysql.fabric.jdbc.FabricMySQLDriver

B: Get a database connection

static Connection getConnection(String url, String user, String password) /* jdbc:mysql://ip Address (domain name): port number/database name Eg:jdbc:mysql://localhost:3306/db1 Local mysql, with port 3306 as default, can be abbreviated: jdbc:mysql:///database name */

A: Get the object that executes sql

//Create a statement object that sends sql statements to the database Statement createStatement() //Create a repareStement object that sends precompiled sql statements to the database PreparedStatement prepareStatement(String sql)

B: Manage Transactions

//Open Transaction: Set the parameter to false to open the transaction setAutoCommit(boolean autoCommit) //Submit Transaction commit() //Rollback transaction rollback()

//Execute DQL (query records (data) of tables in the database) ResultSet executeQuery(String sql) //Perform DML (add or delete data from tables in the database) int executeUpdate(String sql) //Execute any sql statement, but the target is not clear enough to use less boolean execute(String sql) //Place multiple sql statements in the same batch addBatch(String sql) //Send a total batch of sql statements to the database for execution executeBatch()

Code demonstration (with an example of adding a piece of data)

package cn.ideal.jdbc; import java.sql.*; public class StatementDemo { public static void main(String[] args) { Connection connection = null; Statement statement = null; try { //Load Driver Class.forName("com.mysql.jdbc.Driver"); //Get Database Connection Object connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "root99"); //Define sql statement String sql = "INSERT student(id,NAME,score) VALUES (NULL,'Malacca',88);"; //Gets the object that executes the sql statement statement = connection.createStatement(); //Execute sql statement int count = statement.executeUpdate(sql); System.out.println(count); if (count > 0) { System.out.println("Added Successfully"); } else { System.out.println("Failed to add"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally { if(statement != null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }

ResultSet represents the result set of the sql statement - the result of execution, when the Statement object executes excuteQuery(), a ResultSet object is returned

//The cursor moves down a row to determine if the current row is the end of the last row (if there is data) //Returns false if it is, true if it is not boolean next() //Get data, Xxx stands for data type getXxx(parameter) Eg:int getInt() , String getString() 1. int: Number of representative column,From the beginning, such as: getString(1) 2. String: Represents the column name.For example: getDouble("name")

A case can be loaded with a collection framework after attempting to read the data in the database itself, using the Quick Use section at the beginning

(4) Double the effort with half the effort - Tool class

By encapsulating some methods to make a more generic tool class appear, we can make the information more intuitive and easy to maintain through the properties configuration file

package cn.ideal.jdbc; import java.io.FileReader; import java.io.IOException; import java.net.URL; import java.sql.*; import java.util.Properties; public class JDBCUtils { private static String url; private static String user; private static String password; private static String driver; /** * File Read */ static { try { //Create Properties Collection Class Properties pro = new Properties(); //Get files under src path ClassLoader classLoader = JDBCUtils.class.getClassLoader(); URL res = classLoader.getResource("jdbc.properties"); String path = res.getPath(); //load file pro.load(new FileReader(path)); //get data url = pro.getProperty("url"); user = pro.getProperty("user"); password = pro.getProperty("password"); driver = pro.getProperty("driver"); //Register Driver Class.forName(driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * Get Connections * * @return Connecting Objects */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, user, password); } /** * Release Resources * * @param statement * @param connection */ public static void close(Statement statement, Connection connection) { if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * Release Resources * * @param resultSet * @param statement * @param connection */ public static void close(ResultSet resultSet, Statement statement, Connection connection) { 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(); } } } }

Tool Class Test Class

package cn.ideal.jdbc; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class JDBCUtilsTest { public static void main(String[] args) { Connection connection = null; Statement statement = null; try { connection = JDBCUtils.getConnection(); //Define sql statement String sql = "INSERT student(id,NAME,score) VALUES (NULL,'Malacca',88)"; //Gets the object that executes the sql statement statement = connection.createStatement(); //Execute sql statement int count = statement.executeUpdate(sql); System.out.println(count); if (count > 0) { System.out.println("Added Successfully"); } else { System.out.println("Failed to add"); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.close(statement,connection); } } }

In the previous articles, respectively through collection implementation, IO implementation, and learning database, we can try to store data through the database, write a simple login registration case!In the fifth point there is a reference to roaring

(5) Supplement: PreparedStatment

//Create a prepareStatement that sends precompiled sql statements to the database PreparedStatement prepareStatement(String sql)

prepareStatement inherits from Statement and is, in short, stronger and simpler than its parent class!

Statement directly compiles the SQL statement, sends it directly to the database for execution, and repeats the SQL statement several times. PreparedStatement precompiles the SQL and refills the parameters, which is more efficient (precompiled SQL is stored in PreparedStatement)

Variables in Java are often used when defining SQL statements. In some complex cases, quotation marks and single quotation marks are frequently used. The more variables, the more complex, and PreparedStatement can use placeholder'?'Instead of a parameter, assign the parameter next, which makes the code readable

PreparedStatement is precompiled to avoid SQL injection attacks caused by the possible splicing of strings and variables in Statement [Write a permanent equation, bypass password login]

Let's start by writing a simple login to Demo and creating a table!

CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(32), PASSWORD VARCHAR(32) ); SELECT * FROM USER; INSERT INTO USER VALUES(NULL,'admin','admin888'); INSERT INTO USER VALUES(NULL,'zhangsan','123456');

Then write the code

package cn.ideal.login; import cn.ideal.jdbc.JDBCUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class LoginDemo { public static void main(String[] args) { Scanner sc = new Scanner(System.in); System.out.println("enter one user name"); String username = sc.nextLine(); System.out.println("Please input a password"); String password = sc.nextLine(); boolean flag = new LoginDemo().login(username, password); if (flag) { System.out.println("Login Successful"); } else { System.out.println("ERROR Incorrect username or password"); } } /** * Login Method */ public boolean login(String username, String password) { if (username == null || password == null) { return false; } Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = JDBCUtils.getConnection(); //Define sql String sql = "SELECT * FROM USER WHERE username = '" + username + "' AND password = '" + password + "' "; //Get the object that executes sql statement = connection.createStatement(); //Execute Query resultSet = statement.executeQuery(sql); return resultSet.next(); } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtils.close(resultSet,statement, connection); } return false; } }

Simply put, such a simple login to Demo is all right, but at this point, a situation arises in the SQL injection problem. Perhaps you've heard that in the early years, vulnerabilities were still quite common. Some hackers or script boys often used some SQL injection to back-end their target websites.Invasion, one of which we are talking about today, is called SQL Universal Injection (SQL Universal Password)

Let's first look at the section of the above code about the SQL statement

String sql = "SELECT * FROM USER WHERE username = '" + username + "' AND password = '" + password + "' ";

That is, it synthesizes the username and password that we enter into an SQL query statement. When no such field exists in the database, it represents an input error. For programs with SQL injection vulnerabilities, you can construct special strings to log in and test the results by pasting them out first.

What if we replaced the password part of the code above with our own?

String sql = "SELECT * FROM USER WHERE username = 'admin' AND PASSWORD = '1' or '1' = '1' ";

Supplement: Logical operators have precedence in SQL statements, = takes precedence over and, and takes precedence over or

So the AND in the above equation is executed first, of course it returns an error, and then the or part is executed. The return value is always true for a permanent equation''1'='1', so the result of the SQL query is true and you can log in successfully.

​

Ending:

If there are any inadequacies or errors in the content, you are welcome to leave a message for me, Crab and Crab!^^

If you can help, pay attention to me!(The series will be updated at the first time on Public Number)

We don't know each other here, but we are working hard for our dreams.

A public slogan insisting on the original Java technology: more than 20 years

4 August 2019, 00:02 | Views: 3616