Shulou(Shulou.com)06/01 Report--

JDBC:

Although due to the fast-paced development and the pursuit of programming speed, more and more MVC frameworks appear, such as hibernate in the persistence layer.

Mybatis, etc., their support for the Dao layer is very strong, both fast and easy. But they also use JDBC at the bottom.

For the sake of speed and simplicity, we don't have to use JDBC, but we must understand JDBC. Understanding JDBC can also help you learn about other persistence layer frameworks.

The interaction between java and database requires an intermediate program as an intermediary. Long ago, database vendors did not have a unified set of API as

The interface between java language and database, it is a headache to develop programs. Different programs need to be written for different databases as interactions.

Java access-oracle Program-oracle Database

Java access-mysql Program-mysql Database

Java access-db2 Program-db2 Database

So what exactly is JDBC:

JDBC represents the Java database connection, which is the standard Java API for database-independent connections between the java programming language and a wide range of databases.

With JDBC development, there is a lot of unity.

Java access-JDBC Program-oracle Database

Java access-JDBC Program-mysql Database

Java access-JDBC Program-db2 Database

The following introduction JDBC is for oracl and mysql databases.

The implementation class of jdbc interface provided by oracle-ojdbc5.jar-jdk5.0 ojdbc6.jar-jdk6.0

The jdbc interface implementation class provided by mysql-mysql-connector-java-6.0.6

If you want to develop JDBC programs, you must have these jar packages. You can download it on the official website.

Https://dev.mysql.com/downloads/connector/

Brief introduction of API commonly used in JDBC:

Java.sql.Connection: represents a database connection

Java.sql.Statement: send sql statement 1 to the database; (tool for sending sql)

DriverManager: (class) manages multiple database-driven classes

Java.sql.ResultSet: result set, which stores the data results returned after the query statement is executed

The following figure 1. 0 shows the JDBC database access process

Figure 1.0

JDBC development steps

1: load driver

If it is an oracle database:

Copy oracle ojdbc.jar to the project, click on the project, and right-click build path-- add to buidpath

Class.forName ("oracle.jdbc.OracleDriver")

If it is a mysql database:

Copy the mysql mysql-connector-java-6.0.6-bin into the project, build path-- add to buidpath

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

2: connect to the database

User:root---- database user name

Password:root- database password

Url: oracle-Protocol: sub-protocol: thin:@ip: Port: SID (instance name of SID database).

Eg:String url = "jdbc:oracle:thin:@localhost:1521:XE"

Url:mysql-Protocol: subprotocol: / / ip: Port: database name

Eg:String url = "jdbc:mysql://localhost:3306/zdx"

3: prepare sql

You cannot have a semicolon in a sql string

4:Statement sends sql to the database

Int I = executeUpdate (sql); / / returns the number of rows affected

5: if it is a query statement, return the result set and process the ResultSet.

6: close the connection, release resources, and follow the principle of opening first and then closing.

The above 6 steps are the standard steps for developing JDBC programs.

Introduction and usage of ResultSet result set:

ResultSet result set: stores query result data.

ResultSet rs = Statement.executeQuery ()

The initial position of the rs pointer is before the first row of data

Boolean rs.next ():

Move the pointer of rs down one bit, return false with no data in the current row, return true with data, and get the query data.

Provides a set of getXXX (int column serial numbers or String column names) methods for getting data

The above is a brief introduction to JDBC, and the following is an example of JDBC, which is king.

JDBC1.0 version-is version 1.0. There will be a better version to talk about later!

JDBC_ResultSet.java

Public class JDBC_ResultSet {

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

/ / 1: load driver

/ / Class.forName ("oracle.jdbc.OracleDriver")

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

/ / 2: get a database connection

String user = "root"

String password = "root"

/ / String url = "jdbc:oracle:thin:@localhost:1521:XE"

String url = "jdbc:mysql://localhost:3306/zdx?serverTimezone=UTC"

Connection conn = DriverManager.getConnection (url,user,password)

/ / 3: prepare sql

String sql = "select * from student"

/ / 4: create Statement and send sql

Statement stm = conn.createStatement (); / / obtained statement object

/ / 5: if it is a query, process the result set

ResultSet rs = stm.executeQuery (sql)

While (rs.next ()) {

Int id = rs.getInt (1)

String name = rs.getString (2)

Int age = rs.getInt (3)

String phone = rs.getString (4)

System.out.println (id+ "- -" + name+ "-" + age+ "-" + phone)

}

/ / 6: close the connection, release resources, open first and then close

Stm.close ()

Conn.close ()

}

}

At this point, our JDBC1.0 is finished, but why is it 1.0? because there is a big loophole in this version.

Yes, that's the dependency injection problem.

= gorgeous dividing line = =

Let's take a look at this example.

JDBC_Statement.java

Public class JDBC_Statement {

Public static void main (String [] args) throws Exception {

Scanner sc = new Scanner (System.in)

System.out.println ("Please enter card number")

String card_id = sc.nextLine ()

System.out.println ("Please enter password")

String pwd = sc.nextLine ()

/ / 1: load driver

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

/ / 2: get a database connection

String user = "root"

String password = "root"

String url = "jdbc:mysql://localhost:3306/zdx?serverTimezone=UTC"

Connection conn = DriverManager.getConnection (url,user,password)

/ / 3: prepare sql

String sql = "select * from account where card_id =" + card_id

+ "and password ='" + pwd+ "'"

System.out.println (sql)

/ / 4: create Statement and send sql

Statement stm = conn.createStatement (); / / obtained statement object

/ / 5: if it is a query, process the result set

ResultSet rs = stm.executeQuery (sql)

If (rs.next ()) {

System.out.println ("ready to withdraw money")

} else {

System.out.println ("incorrect password account")

}

/ / 6: close the connection, release resources, open first and then close

Stm.close ()

Conn.close ()

}

}

This is an example of a bank withdrawing money, as written in the code.

The database has such an account table, the data and fields are as follows:

Now enter the card number password in the console. If the card number password is correct, you can withdraw the money.

An error indicates that the account is incorrect.

Now enter the correct card number and password in the console

Then, I entered the wrong card number password can also withdraw money! Enter the wrong card number "123 or 1: 1 -" password "zxc" as follows

It can be seen that you can still access the database, which is a fatal mistake for the bank, and the wrong password can also withdraw money.

This is the famous error caused by dependency injection.

Destroy the sql structure by human input.

Select * from account where id = 1001 or 1-- and password = 'xxx'

In this sql, where-- is the comment or 1 in sql that is always true and still an or connection

So this sql is only executed until or 1, which is identical again. So I skipped the password.

In order to solve this problem, we have to say PreparedStatement!

Introduction and use of PreparedStatement

PreparedStatement builds dynamic SQL, and executes SQL statements through PreparedStatement to solve the injection *.

PreparedStatement is a subinterface of Statement.

Create a PreparedStatement through a connection, using the placeholder "?" for the changed part of the SQL statement. Instead.

Function: send sql statements just like statement.

But it is efficient to execute multiple isomorphic sql. Isomorphic sql can omit the ①②③ step.

Steps to use:

1. Create pstm

String sql = "select * from account where card_id =? and password =?"

PreparedStateement pstm = conn.prepareStatement (sql)

① verify permissions

② validation syntax

③ converts sql to internal instructions

two。 Binding parameter

Pstm.setInt (1, value)

Pstm.setString (2, value)

3. Send binding parameters to the DB database

Pstm.executeUpdate (); / / has been deleted

Pstm.executedQuery (); / / query

④ executes internal instructions to operate the database

Perform sql steps internally in mysql:

① verify permissions

② validation syntax

③ converts sql to internal instructions

④ executes internal instructions to operate the database

The code after using PareparedStatement to solve the injection * problem is as follows:

The JDBC2.0 version is version 2.0 > _ <. There will be a better version to talk about later!

JDBC_PreparedStatement.java

Public class JDBC_PreparedStatement {

Public static void main (String [] args) throws Exception {

Scanner sc = new Scanner (System.in)

System.out.println ("Please enter card number")

String card_id = sc.nextLine ()

System.out.println ("Please enter password")

String pwd = sc.nextLine ()

/ / 1: load driver

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

/ / 2: get a database connection

String user = "root"

String password = "root"

String url = "jdbc:mysql://localhost:3306/zdx?serverTimezone=UTC"

Connection conn = DriverManager.getConnection (url,user,password)

/ / 3: prepare sql

String sql = "select * from account where card_id =? and password =?"

PreparedStatement pstm = conn.prepareStatement (sql)

Pstm.setInt (1, Integer.valueOf (card_id))

Pstm.setString (2, pwd)

/ / 5: if it is a query, process the result set

ResultSet rs = pstm.executeQuery ()

If (rs.next ()) {

System.out.println ("ready to withdraw money")

} else {

System.out.println ("incorrect password account")

}

/ / 6: close the connection, release resources, open first and then close

Rs.close ()

Pstm.close ()

Conn.close ()

}

}

Now use the PreparedStatement program to execute, enter the card number, password

If there is an exception, there will be no problem of withdrawing money with a wrong password!

Summarize and compare Statement and PreparedStatement

Statement

PreparedStatement

Relationship

Parent interface

Subinterface

Safety

There is a hidden danger of injection

Resolve sql injection

Efficiency

Execute heterogeneous sql fast

Execute isomorphic sql fast

This is the end of JDBC2.0. The basic content of JDBC is over, and the following is the advanced content of JDBC. And the optimized version of JDBC.

= gorgeous dividing line = =

Due to the limited space, please refer to the next blog in the following section. From shallow to deep learning JDBC II

Readers are urged to criticize and correct the irregularities and make progress together.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.