MySQL Learning (iii) -- connecting to the MySQL database using Java and javamysql

MySQL Learning (iii) -- connecting to the MySQL database using Java and javamysql

1. What is JDBC?

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

2. JDBC principles

The JDBC specification provided by SUN and the implementation class provided by the manufacturer are called drivers.

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.

3. JDBC development steps

1) Registration driver

2) obtain the connection

3) obtain the statement executor

4) Execute SQL statements

5) processing result

6) release resources

3. Import the driver jar package

1) create a project named WEB08_JDBC,

2) Create the lib directory, right-click New-> Folder, and name it lib to store all the jar packages required by the current project,

Copy the jar package to the lib folder of the current project,

3) Right-click the jar package and execute Build Path until a bottle icon appears in the current directory.

4. Test SQL injection (applicable to JUnit unit test)

In mysql, The tbl_user table in the web08 database contains two pieces of data. log on to the table based on the user information.

The Code is as follows:

1 package cn. itheima. test; 2 3 import java. SQL. connection; 4 import java. SQL. driverManager; 5 import java. SQL. preparedStatement; 6 import java. SQL. resultSet; 7 import java. SQL. SQLException; 8 import java. SQL. statement; 9 10 import org. junit. test; 11 12 public class TestLogin {13 @ Test14 public void testLogin () {15 try {16 login1 ("zhangsan", "999"); 17} catch (Exception e) {18 e. printStackTrace (); 19} 2 0} 21 public void login1 (String username, String password) throws ClassNotFoundException, SQLException {22/* 23 * User Logon Method 24 */25 // 1. register driver 26 Class. forName ("com. mysql. jdbc. driver "); 27 // 2. get Connection 28 conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/web08", "root", "12345"); 29 // 3. write SQL statement 30 String SQL = "select * from tbl_user where uname =? And upassword =? "; 31 // 4. create a preprocessing object 32 PreparedStatement pstmt = conn. prepareStatement (SQL); 33 // 5. set parameters (to placeholder) 34 pstmt. setString (1, username); 35 pstmt. setString (2, password); 36 // 6. perform the query operation 37 ResultSet rs1_pstmt.exe cuteQuery (); 38 // 7. processing the result set 39 if (rs. next () {40 System. out. println ("congratulations," + username + "Login successful! "); 41} else {42 System. out. println (" incorrect account or password! "); 43} 44 if (rs! = Null) rs. close (); 45 if (pstmt! = Null) pstmt. close (); 46 if (conn! = Null) conn. close (); 47} 48}

Right-click Run As-> JUnit Test, and the execution result is: Congratulations, zhangsan is successfully logged on!