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

Getting started with 1.JDBC

1. JDBC (Java Data Base Connectivity)

1. Database driver:

A set of jar packages provided by database vendors to facilitate developers to operate the database from the program. By importing this jar package, you can call the methods in it to operate the database. Such jar packages are called database drivers.

2 、 JDBC:

A set of standards defined by sun is essentially a large number of interfaces for operating databases, which have been implemented by database drivers designed by all database manufacturers for java. In this way, different database-driven methods are unified, and developers only need to learn JDBC to use any database driver.

There are six steps to implement JDBC:

/ / 1. Register the database driver

Because mysql registers itself once in the implementation of the Driver class, and we register again, this causes the MySql driver to be registered twice.

-when creating the Driver object of MySql, the program and the specific Mysql driver are tied together, and the java code needs to be changed when switching the database.

/ / DriverManager.registerDriver (new Driver ())

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

/ / 2. Send a connection to the database server from the client, which host, which port and which database to url=

Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/day10", "root", "root")

/ / 3. Gets the transporter object. Connection = road; transmitter = truck, carrying data.

Statement stat = conn.createStatement ()

/ / 4. Use the transporter to transfer the sql statement to the database for execution and get the result set object

ResultSet rs = stat.executeQuery ("select * from user")

/ / 5. Traverse the result set to get the query result

/ / there is a small cursor pointing to the result set one by one, and the last line is empty and returns false.

/ / ResultSet is used to represent the execution result of the Sql statement. Encapsulates the execution results in a table-like manner. The ResultSet object maintains a cursor pointing to the row of table data. Initially, the cursor calls the ResultSet.next () method for the first time before the first row, and the cursor points to the first row of data, and again calls the ResultSet.next () method to point to the next row, that is, the second row of data.

While (rs.next ()) {String name = rs.getString ("name")

System.out.println (name)

}

/ / 6. Close the resource

Rs.close ()

Stat.close ()

Conn.close ()

2.JDBC details

Connection object, which is a very rare resource, must be released immediately after use. If the Connection is not shut down in time and correctly, it can easily lead to system downtime.

To ensure that the resource release code runs, the resource release code must also be placed in the finally statement.

Connection conn = null

Statement stat = null

ResultSet rs = null

Try {. .

} catch (Exception e) {

E.printStackTrace ()

} finally {

/ / 6. Close the resource

If (rswatches null) {

Try {

Rs.close ()

} catch (SQLException e) {

E.printStackTrace ()

} finally {

Rs = null

}

}

If (statuses null) {

Try {

Stat.close ()

} catch (SQLException e) {

E.printStackTrace ()

} finally {

Stat = null

}

}

If (connexual null) {

Try {

Conn.close ()

} catch (SQLException e) {

E.printStackTrace ()

} finally {

Conn = null

}

}

}

3.JDBC addition, deletion, modification and search

Config.properties

Driver=com.mysql.jdbc.Driver

Url=jdbc:mysql:///day10

User=root

Password=root

JDBCUtils.java

Public class JDBCUtils {

Private staticProperties prop = null

Private JDBCUtils () {

}

Static {

Try {

Prop = new Properties ()

Prop.load (new FileReader (JDBCUtils.class.getClassLoader (). GetResource ("config.properties") .getPath ()

} catch (Exception e) {

E.printStackTrace ()

Throw new RuntimeException (e)

}

}

/ / Connect

Public static Connection getConn () throws ClassNotFoundException, SQLException {

/ / 1. Register the database driver

Class.forName (prop.getProperty ("driver"))

/ / 2. Get connection

Return DriverManager.getConnection (prop.getProperty ("url"), prop.getProperty ("user"), prop.getProperty ("password"))

/ / close

Public static void close (ResultSet rs, Statement stat,Connection conn) {.}

JDBCDemo2.java

@ Test

Public void update () {

Connection conn = null

Statement stat = null

Try {

Conn = JDBCUtils.getConn ()

Stat = conn.createStatement ()

Stat.executeUpdate ("update user set password=999 where name='zhaoliu'")

} catch (Exception e) {

E.printStackTrace ()

} finally {

JDBCUtils.close (null, stat, conn)

}

}

4. Transform the User case

The concept of layer-to-layer coupling, using factory class decoupling, how does service use dao? DaoFactory.getFactory () .getDao ()

Public class DaoFactory {

Private static DaoFactory factory = new DaoFactory ()

Private static Properties prop = null

Static {try {

Prop = new Properties ()

Prop.load (new FileReader (DaoFactory.class.getClassLoader (). GetResource ("config.properties") .getPath ()

} catch (Exception e) {e.printStackTrace ()

Throw new RuntimeException (e);}}

Private DaoFactory () {}

Public static DaoFactory getFactory () {

Return factory;}

Public UserDao getDao () {

Try {

String clazz = prop.getProperty ("UserDao")

Return (UserDao) Class.forName (clazz). NewInstance ()

} catch (Exception e) {e.printStackTrace ()

Throw new RuntimeException (e);}

5.PreparedStatement prevents sql injection from SQL injection * *:

Because the SQL statements executed in dao are spliced, some of the contents are passed in by the user from the client, so when the data passed by the user contains the sql keyword, it is possible to change the semantics of the sql statement through these keywords to perform some special operations. This way is called sql injection.

PreparedStatement:

The precompiled mechanism is used to transfer the trunk and parameters of sql statements to the database server respectively, so that the database can distinguish which are the backbone of sql statements and which are parameters. In this way, even if the keyword sql is included in the parameter, the database server will only use it as a parameter value, and the keyword will not work, thus preventing the problem of sql injection in principle.

PreparedStatement has the following three main advantages:

1. Can prevent sql injection

two。 Due to the use of precompilation mechanism, the execution efficiency is higher than that of Statement.

Use the 3.sql statement? Replace the parameter with the form, and then set it with the method? The code is more elegant than concatenating strings.

6. Large text large binary

What is stored in the database is the path of big data. Big data is stored on the hard disk. It is time-consuming and laborious to read big data from the database.

You just need to know.

* JDBC big data

Text Blob

1.1 set the Text type

PreparedStatement.setCharacterStream (index, reader, length)

/ / Note that the length length must be set and set to int.

/ / modify the configuration when the package is too large: [mysqld] max_allowed_packet=64M

1.2 get Text type

Reader = resultSet. GetCharacterStream (I)

2.1 set the BLOB data type

PreparedStatement. SetBinaryStream (I, inputStream, length)

2.1 get the BLOB type

InputStream in = resultSet.getBinaryStream (I)

InputStream in = resultSet.getBlob (I) .getBinaryStream ()

Public class BlobDemo1 {

@ Test

Public void findBlob () {

Connection conn = null

PreparedStatement ps = null

ResultSet rs = null

Try {

Conn = JDBCUtils.getConn ()

Ps = conn.prepareStatement ("select * from blobdemo")

Rs = ps.executeQuery ()

While (rs.next ()) {

String name = rs.getString ("name")

InputStream in = rs.getBinaryStream ("content")

OutputStream out = new FileOutputStream (name)

Byte [] bs = new byte [1024]

Int I = 0

While ((i=in.read (bs))! =-1) {

Out.write (bs,0,i)

}

In.close ()

Out.close ()

}

} catch (Exception e) {

E.printStackTrace ()

} finally {

JDBCUtils.close (rs, ps, conn)

}

}

@ Test

Public void addBlob () {

Connection conn = null

PreparedStatement ps = null

ResultSet rs = null

Try {

Conn = JDBCUtils.getConn ()

Ps = conn.prepareStatement ("insert into blobdemo values (null,?,?)")

Ps.setString (1, "Luo Tianyi .mp3")

File file = new File ("1.mp3")

Ps.setBinaryStream (2, new FileInputStream (file), (int) file.length ())

Ps.executeUpdate ()

} catch (Exception e) {

E.printStackTrace ()

} finally {

JDBCUtils.close (rs, ps, conn)

}

}

}

7. Batch processing mechanism

Business scenario: when you need to send a batch of SQL statements to the database for execution, you should avoid sending and executing one by one to the database. Instead, you should use the batch processing mechanism of JDBC to improve the execution efficiency.

There are two ways to implement batch processing, the first one:

Statement.addBatch (sql) executes batch SQL statements

ExecuteBatch () method: executes batch commands

ClearBatch () method: clears the batch command

Connection conn = null

Statement st = null

ResultSet rs = null

Try {

Conn = JdbcUtil.getConnection ()

String sql1 = "insert into person (name,password,email,birthday)

Values ('kkk','123','abc@sina.com','1978-08-08') "

String sql2 = "update user set password='123456' where id=3"

St = conn.createStatement ()

St.addBatch (sql1); / / add the SQL statement to the batch command

St.addBatch (sql2); / / add the SQL statement to the batch command

St.executeBatch ()

} finally {

JdbcUtil.free (conn, st, rs)

}

Batch processing is implemented by Statement.addBatch (sql):

Pros: you can send multiple different SQL statements to the database.

Disadvantages:

The SQL statement is not precompiled.

When multiple SQL statements with the same statements but only different parameters are sent to the database, many SQL statements need to be written repeatedly.

The second way to implement batch processing:

PreparedStatement.addBatch ()

Conn = JdbcUtil.getConnection ()

String sql = "insert into person (name,password,email,birthday) values (?)"

St = conn.prepareStatement (sql)

For (int iTuno Bandi)

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.