Java web ---- JDBC

Java web ---- JDBC

1 What is JDBC?

JDBC (Java DataBaseConnectivity) is a Java database connection. To put it bluntly, it uses the Java language to operate the database. In the past, we used SQL statements on the console to operate databases, while JDBC used Java to send SQL statements to databases.

JDBC is the interface, while JDBC driver is the implementation of the interface. No driver can complete database connection! Each database vendor has its own driver to connect to its own company's database.

2 Introduction to JDBC core classes (interfaces)

The core classes in JDBC include DriverManager, Connection, Statement, and ResultSet!

DriverManger (driver manager) has two functions:

Register the driver: This allows JDBC to know which driver to use; obtain Connection: If you can obtain the Connection, it indicates that it has been connected to the database.

The Connection object indicates the Connection, and the communication with the database is expanded through this object:

The most important method of Connection is to obtain the Statement object;

Statement is used to send SQL statements to the database, so that the database will execute the SQL statements sent:

Void executeUpdate (String SQL): Execute the update operation (insert, update, delete, etc.); ResultSet executeQuery (String SQL): Execute the query operation. After the database executes the query, it returns the query result, the query result is ResultSet;

The ResultSet object indicates the query result set. The result set is generated only after the query operation is executed. The result set is a two-dimensional table with rows and columns. The operation result set should learn to move the row cursor inside the ResultSet and obtain the data in each column of the current row:

Boolean next (): Move the "Row cursor" to the next row and return whether the row to be moved exists. XXX getXXX (int col): gets the value of the specified column in the current row, the parameter is the number of columns. The number of columns starts from 1, not 0.

3. The first JDBC Program

3.1 mysql database driver jar package: mysql-connector-java-5.1.13-bin.jar;

3.2 obtain the connection

Two steps are required to obtain the Connection. One is to use DriverManager to register the driver, and the other is to use DriverManager to obtain the Connection object.

1. Registration driver

As you can see, the registration Driver has only one sentence: Class. forName ("com. mysql. jdbc. Driver"). The following describes the code. In our code in the future, there will be only one sentence related to the registration driver.

The parameter of the registerDriver () method of the DriverManager class is java. SQL. driver, but java. SQL. the Driver is an interface and the implementation class is provided by the mysql Driver. SQL. the implementation class of the Driver interface is com. mysql. jdbc. driver! The registration driver code is as follows:

DriverManager. registerDriver (new com. mysql. jdbc. Driver ());

Although the above Code can be used to register a driver, it is hard-coded (the Code depends on the mysql driver jar package). If you want to connect to the Oracle database in the future, you must modify the code. In fact, this registration-driven method is to register two drivers!

JDBC stipulates that when a Driver class is loaded, you need to manually register yourself to DriverManger. Let's take a look at the source code of the com. mysql. jdbc. Driver Class:

Com. mysql. jdbc. Driver. java

public class Driver extends NonRegisteringDriver implements java.sql.Driver {static {try {java.sql.DriverManager.registerDriver(new Driver());} catch (SQLException E) {throw new RuntimeException("Can't register driver!");}}public Driver() throws SQLException {// Required for Class.forName().newInstance()}}

The static block in the com. mysql. jdbc. Driver Class creates this class object and registers it to DriverManager. This indicates that you only need to load com. mysql. jdbc. driver Class, then the static block will be executed, and thus the com. mysql. jdbc. the Driver is registered in DriverManager, so you canRegister Driver ClassToLoad Driver Class.

Class. forName ("com. mysql. jdbc. Driver ");

2. Get the connection

There is only one code to get the connection: DriverManager. getConnection (url, username, password), where username and password are the username and password used to log on to the database. If I am not wrong, your mysql database username and password are: root, 123.

Url lookup is a bit more complex. It is used to find the "url" of the database to be connected. For example, if you want to search Baidu in a browser, you also need to provide a url. The url of mysql is as follows:

Jdbc: mysql: // localhost: 3306/mydb1

JDBC requires that the url format consists of three parts, separated by commas.

The first part is jdbc, which is fixed. The second part is the database name. Therefore, when connecting to the mysql database, the second part is of course mysql. The third part is specified by the database vendor, we need to understand the requirements of each DATABASE vendor. The third part of mysql is composed of the IP address (localhost), Port Number (3306), and DATABASE name (mydb1) of the DATABASE server.

The following statements are used to obtain the connection:

Connection con = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/mydb1", "root", "123 ");

You can also provide parameters in the url:

Jdbc: mysql: // localhost: 3306/mydb1? UseUnicode = true & characterEncoding = UTF8

The useUnicode parameter specifies that the byte set used when the database is connected is a Unicode byte set;

The characherEncoding parameter specifies the encoding of the byte set used as the UTF-8 during database connection. Note that the UTF-8 encoding specified in mysql is UTF8, not UTF-8. Be careful!

3.3 get Statement

After obtaining Connectoin, it indicates that it has been connected to the database. The following code obtains the Statement object through Connection:

Statement stmt = con. createStatement ();

Statement is used to send SQL statements to be executed to the database!

3.4 send SQL add, delete, and modify statements

String SQL = "insertinto user value ('hangsan', '123 ')";

Int m merge stmt.exe cuteUpdate (SQL );

The Return Value of the int type indicates the number of rows affected by the execution of this SQL statement. We know that for insert, only one row can be affected at last, while update and delete may affect 0 ~ N rows.

If the SQL statement fails to be executed, executeUpdate () throws an SQLException.

3.5 send SQL query statements

String SQL = "select * from user ";

ResultSet rs into stmt.exe cuteQuery (SQL );

Please register. The executeUpdate () method is not used for execution query, but the executeQuery () method. The executeQuery () method returns the ResultSet, which encapsulates the query results.

3.6 read data from the result set

ResultSet is a two-dimensional table with a "Row cursor" inside. The cursor is located at the top of the first row by default. We can call next () of the rs object () the method moves the row cursor down a row. When the next () method is called for the first time, the row cursor is at the position recorded in the first row, in this case, you can use the getXXX (intcol) method provided by ResultSet to obtain the data of the specified column:

Rs. next (); // move the cursor to the first line

Rs. getInt (1); // obtain the data in the first column of the First row

When you use the rs. getInt (1) method, you must be sure that the data type of the 1st column is the int type. If you are not sure, it is best to use rs. getObject (1 ). The ResultSet class provides a series of getXXX () methods, which are commonly used:

ObjectgetObject (int col)

String getString (intcol)

Int getInt (intcol)

DoublegetDouble (int col)

3.7 close

Like the IO stream, you need to disable all things after use! The order of close is obtained first and then closed.

Rs. close ();

Stmt. close ();

Con. close ();

Code 3.8

Public static Connection getConnection () throws Exception {Class. forName ("com. mysql. jdbc. driver "); String url =" jdbc: mysql: // localhost: 3306/mydb1 "; return DriverManager. getConnection (url, "root", "123") ;}@ Testpublic void insert () throws Exception {Connection con = getConnection (); Statement stmt = con. createStatement (); String SQL = "insert into user values ('hangsan', '000000')" using stmt.exe cuteUpdate (SQL ); System. out. println ("inserted successfully! ") ;}@ Testpublic void update () throws Exception {Connection con = getConnection (); Statement stmt = con. createStatement (); String SQL = "update user set password = '000000' where username = 'hangsan'" using stmt.exe cuteUpdate (SQL); System. out. println ("modified successfully! ") ;}@ Testpublic void delete () throws Exception {Connection con = getConnection (); Statement stmt = con. createStatement (); String SQL = "delete from user where username = 'hangsan'" Maid (SQL); System. out. println ("deleted successfully! ") ;}@ Testpublic void query () throws Exception {Connection con = getConnection (); Statement stmt = con. createStatement (); String SQL = "select * from user"; ResultSet rs = stmt.exe cuteQuery (SQL); while (rs. next () {String username = rs. getString (1); String password = rs. getString (2); System. out. println (username + "," + password );}}

3.9 normative code

The so-called canonicalized code is to disable ResultSet, Statement, and Connection regardless of whether an exception occurs. If you still remember the canonicalized code of the IO stream, you will understand the following code.

@Testpublic void query() {Connection con = null;Statement stmt = null;ResultSet rs = null;try {con = getConnection();stmt = con.createStatement();String sql = "select * from user";rs = stmt.executeQuery(sql);while(rs.next()) {String username = rs.getString(1);String password = rs.getString(2);System.out.println(username + ", " + password);}} catch(Exception e) {throw new RuntimeException(e);} finally {try {if(rs != null) rs.close();if(stmt != null) stmt.close();if(con != null) con.close();} catch(SQLException e) {}}}

4. Introduction to JDBC objects
4.1 main classes (interfaces) in JDBC)

Common JDBC classes include:

DriverManager; Connection; Statement; ResultSet.

4.2 DriverManager

In fact, we only need to use the getConnection () method of DriverManager in the future:

1. Class. forName ("com. mysql. jdbc. Driver"); // register the Driver

2. String url = "jdbc: mysql: // localhost: 3306/mydb1 ";

3. String username = "root ";

4. String password = "123 ";

5. Connection con = DriverManager. getConnection (url, username, password );

Note: The preceding code may have two exceptions:

1. ClassNotFoundException: this exception occurs in the 1st sentence. There are two possibilities for this exception:

You didn't provide the mysql jar package; you typed the class name incorrectly to check whether the class name is com. mysql. jdbc. Driver.

2. SQLException: this exception occurs in the first sentence. This exception is caused by three parameters. Generally, username and password are not incorrect. Therefore, check whether the url is wrong.

You can understand the DriverManager. registerDriver () method, because we will only register Class. forName () for the driver in the future, instead of using this method.

4.3 Connection

The most important method of Connection is to get Statement:

Statement stmt = con. createStatement ();

When you are learning the ResultSet method, you also need to learn the following methods:

Statement stmt = con. createStatement (int, int );

4.4 Statement

The most important method of Statement is:

Int executeUpdate [c1] (String SQL): Execute the update operation, that is, execute the insert, update, and delete statements. In fact, this method can also execute create table, alter table, and drop table statements, but we seldom use JDBC to execute these statements;

ResultSet executeQuery [c2] (String SQL): executes the query operation. If you perform the query operation, the ResultSet is returned, that is, the result set.

Boolean execute [c3] ()

Statement also has a boolean execute () method, which can be used to execute add, delete, modify, and query all SQL statements. This method returns a boolean type, indicating whether the SQL statement has a result set !.

If you use the execute () method to execute an update statement, you must call int getUpdateCount () to obtain the number of rows affected by the insert, update, and delete statements.

If you use the execute () method to execute a query statement, you must call ResultSet getResultSet () to obtain the query result of the select statement.

4.5 rolling result set of ResultSet)

Next line: it can only be used by default. Other methods exist, but cannot be used! The default result set cannot be rolled!

Last line

Next N rows

N rows above

To N rows!

ResultSet indicates the result set. It is a two-dimensional table! The ResultSet maintains a row cursor. The ResultSet provides a series of methods to move the cursor:

Void beforeFirst (): place the cursor in front of the first line, which is also the default position of the cursor; void afterLast (): place the cursor behind the last line; boolean first (): place the cursor at the position of the first line. The returned value indicates whether the cursor is successfully controlled. boolean last (): place the cursor at the position of the last line;
Boolean isBeforeFirst (): indicates whether the cursor is located before the first line. boolean isAfterLast () indicates whether the cursor is located behind the last line. boolean isFirst (): whether the current cursor position is on the first line; boolean isLast (): whether the current cursor position is on the last line; boolean previous (): move the cursor up line; boolean next (): move the cursor down a row; boolean relative (int row): relative displacement. When the row is a positive number, it indicates moving the row down. If the row is a negative number, it indicates moving the row up; boolean absolute (int row): absolute displacement. move the cursor to the specified row. int getRow (): returns all rows of the current cursor.

4.6 retrieve column data from ResultSet

You can use the next () method to move the ResultSet cursor downward. When the cursor moves to the required row, you need to obtain the data of the row, resultSet provides a series of methods to obtain column data:

String getString (int columnIndex): Get the String type data of the specified column; int getInt (int columnIndex): Get the int type data of the specified column; double getDouble (int columnIndex ): get the double type data of the specified column; boolean getBoolean (int columnIndex): Get the boolean type data of the specified column; Object getObject (int columnIndex): Get the Object type data of the specified column.

In the preceding method, the columnIndex parameter indicates the column index. The column index starts from 1 rather than 0. The first point is different from the array. If you are clear about the data type of the current column, you can use methods such as getInt () to obtain it. If you are not clear about the column type, you should use the getObject () method to obtain it.

ResultSet also provides a set of methods to obtain column data by column name [c1]: String getString (String columnName): Get the String data of a column named columnName; int getInt (String columnName): Get the int data of a column named columnName; double getDouble (String columnName): Get the double data of a column named columnName; boolean getBoolean (String columnName): Get boolean data for columnName columns; Object getObject (String columnName): Get Object data for columnName columns;

5 PreparedStatement

It is a subinterface of the Statement interface;

Powerful:

Prevents SQL attacks, improves code readability and maintainability, and improves efficiency!

Learn how to use PreparedStatement:

How to get the PreparedStatement object:

An SQL template is provided! Call the PreparedStatement prepareStatement of Connection (String SQL template); call the question mark (?) in the setXxx () series method SQL template of pstmt? Assignment! Call the executeUpdate () or executeQuery () of pstmt, but its method does not have any parameters.

Principles of preprocessing

Server work:

Verify SQL statement syntax! Compile: something similar to a function! Run: Call the function.

PreparedStatement:

Prerequisites: The connected database must support preprocessing! Almost none! Each pstmt is bound with an SQL template. The SQL template is first sent to the database, and the database is verified before compilation. During execution, the parameter is passed! If you perform the second execution, you do not need to verify the syntax again or compile it again! Run it directly!

5.1 What is SQL attack

Where user input is required, the user enters the SQL statement segment. The SQL segment entered by the end user is combined with the SQL statement written in DAO to form a complete SQL statement! For example, the user name and password entered during login are SQL statement fragments!

5.3 demonstrate SQL attacks

CREATE TABLE user(uidCHAR(32) PRIMARY KEY,usernameVARCHAR(30) UNIQUE KEY NOT NULL,PASSWORD VARCHAR(30));INSERT INTO user VALUES('U_1001', 'zs', 'zs');SELECT * FROM user;

Currently, there is only one record in the User table, that is, zs.

Let's write a login () method!

Public void login (String username, String password) {Connection con = null; Statement stmt = null; ResultSet rs = null; try {con = JdbcUtils. getConnection (); stmt = con. createStatement (); String SQL = "SELECT * FROM user WHERE" + "username = '" + username + "' and password = '" + password + "'"; rs = stmt.exe cuteQuery (SQL); if (rs. next () {System. out. println ("welcome" + rs. getString ("username");} else {System. out. Println ("incorrect user name or password! ") ;}} Catch (Exception e) {throw new RuntimeException (e) ;}finally {JdbcUtils. close (con, stmt, rs );}}

The following code calls this method:

Login ("A' or 'A' = 'a", "A' or 'A' = 'a ");

This line will allow us to log on successfully now! Because the user name and password entered are SQL statement fragments, they are combined with the SQL statements in our login () method! Let's take a look at the combined SQL statements:

SELECT * FROM tab_user WHERE username ='A' or 'A' = 'a'And password ='A' or 'A' = 'a'

5.4 prevent SQL attacks

Checks whether the data entered by the user contains invalid characters! First use the user name to query the user, if found, then compare the password; Use PreparedStatement.

5.5 what is PreparedStatement?

PreparedStatement is called the pre-compilation statement!

PreparedStatement is a subinterface of Statement. You can use PreparedStatement to replace Statement.

Benefits of PreparedStatement:

Prevents SQL attacks, improves code readability, maintainability, and improves efficiency.

5.6 use of PreparedStatement

Use the prepareStatement (Stringsql) of the Connection: bind it to an SQL template when it is created; call the setXXX () series of methods of PreparedStatement to set the question mark value and call executeUpdate () or executeQuery () method, but note that the method without parameters is called;

String sql = “select * from tab_student where s_number=?”;PreparedStatement pstmt = con.prepareStatement(sql);pstmt.setString(1, “S_1001”);ResultSet rs = pstmt.executeQuery();rs.close();pstmt.clearParameters();pstmt.setString(1, “S_1002”);rs = pstmt.executeQuery();

When using Connection to create a PreparedStatement object, you must provide an SQL template. The so-called SQL template contains "?" SQL statement, where "?" Is the parameter.

After obtaining the PreparedStatement object, call its setXXX () method as "?" Assign a value to convert the Template into a complete SQL statement, and then call the executeQuery () method of the PreparedStatement object to obtain the ResultSet object.

Note that the executeQuery () method exclusive to the PreparedStatement object has no parameters, while the executeQuery () method of Statement requires parameters (SQL statements. Because the PreparedStatement object has been bound to an SQL template, parameters are no longer required when the executeQuery () and executeUpdate () methods are called.

The biggest benefit of PreparedStatement is to reuse the same template and give different parameters to reuse it. This is the reason for improving efficiency.

package com.cug.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.junit.Test;public class Demo04 {public boolean login2(String username, String password){Connection conn = null;PreparedStatement pst = null;ResultSet rs = null;String driverClass = "com.mysql.jdbc.Driver";String url = "jdbc:mysql://localhost:3306/mydb1";String dbUsername = "root";String dbPassword = "123";try {Class.forName(driverClass);conn = DriverManager.getConnection(url,dbUsername,dbPassword);pst = conn.prepareStatement("select * from user where username=? and password = ?");pst.setString(1, username);pst.setString(2, password);rs = pst.executeQuery();return rs.next();} catch (ClassNotFoundException e) {e.printStackTrace();} catch(SQLException e){e.printStackTrace();} finally{try {if(rs != null) rs.close();if(pst != null) pst.close();if(conn != null) conn.close();} catch (SQLException e) {e.printStackTrace();}}return false;}@Testpublic void fun2(){String username = "zs";String password = "zs";boolean b = login2(username, password);System.out.println(b);}}

Therefore, we recommend that you use PreparedStatement in future development, regardless of the situation.

6 JdbcUtils tool class
Dbconfig. properies

driverClassName=com.mysql.jdbc.Driverurl=jdbc\:mysql\://localhost\:3306/mydb1username=rootpassword=123

JdbcUtils. java

package com.cug.jdbc;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;public class jdbcUtils {private static Properties props = null;static{props = new Properties();InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("dbconfig.properties");try {props.load(in);} catch (IOException e) {e.printStackTrace();}try {Class.forName(props.getProperty("driverClassName"));} catch (ClassNotFoundException e) {e.printStackTrace();}}public static Connection getConnection() throws SQLException{return DriverManager.getConnection(props.getProperty("url"), props.getProperty("username"), props.getProperty("password"));}}

package com.cug.jdbc;import java.sql.Connection;import java.sql.SQLException;import org.junit.Test;public class Demo05 {@Testpublic void fun3() throws SQLException{Connection conn = jdbcUtils.getConnection();System.out.println(conn);Connection conn1 = jdbcUtils.getConnection();System.out.println(conn1);}}