Database topic of SQL Study Notes (iv): Introduction to JDBC usage and Study Notes jdbc

Database topic of SQL Study Notes (iv): Introduction to JDBC usage and Study Notes jdbc

The jar package provided by the database vendor for database operations is the database driver. If each vendor provides their own database driver, it will lead to a high learning cost for developers. Therefore, sun provides a set of interface specifications that should be followed by the database driver, which is called JDBC, it is essentially a lot of interfaces. In short, JDBC is a set of database operation interface specifications. Since all database drivers follow the JDBC specifications, we only need to learn the interfaces in JDBC when learning and using databases.

Two JDBC packages: java. SQL, javax. SQL, JDBC application development requires the support of the above two packages, you also need to import the corresponding JDBC database implementation (that is, the database driver ).

Let's take a look at the Use steps of JDCB:

* Create a table in the database
* Import the database driver package to the program.
1. register the database driver
DriverManager. registerDriver (new Driver (); // disadvantage 1: Check the mysqlDriver source code and find that this method causes the database Driver to be registered twice. Disadvantage 2: coupling is added to the mysql database driver binding in the entire program domain.
Class. forName ("com. mysql. jdbc. Driver ");
2. Get the connection
DriverManager. getConnection (url, user, password );
~ Url Syntax:
Oracle: jdbc: oracle: thin: @ localhost: 1521: sid
SqlServer-jdbc: microsoft: sqlserver: // localhost: 1433; DatabaseName = sid
MySql-jdbc: mysql: // localhost: 3306/sid
~ Url parameters
User and password
UseUnicode = true & characterEncoding = UTF-8


3. Get the transmitter
CreateStatement (): Creates a statement object that sends SQL statements to the database.
PrepareStatement (SQL): Creates a PrepareSatement object that sends pre-compiled SQL statements to the database.
4. Use the transmitter to execute SQL statements to obtain the result set.
ExecuteQuery (String SQL): used to send query statements to Data.
ExecuteUpdate (String SQL): used to send insert, update, or delete statements to the database.
Execute (String SQL): used to send arbitrary SQL statements to the database


5. Retrieve the structure of the result set through Traversal
The ResultSet saves the query results in the memory in a table style, and maintains a cursor. At the beginning, the cursor is before the first row, and each call of next () method To move a row down. If the row is successfully moved, true is returned;
ResultSet also provides many Get methods to obtain different types of data in the query results.
In addition to the next method, the following methods can be used to traverse the result set:
Next (): Move to the next row
Previous (): Move to the Previous row
Absolute (int row): Move to the specified row
BeforeFirst (): Move the front of the resultSet.
AfterLast (): Move to the end of the resultSet.
6. release resources
Conn is a limited resource, and tables to be released immediately after use
Stat occupies memory, so it should be released after use
Rs occupies memory, so it must be released after use
Release first created after release
If (rs! = Null ){
Try {
Rs. close ();
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
Rs = null;
}
}
If (stat! = Null ){
Try {
Stat. close ();
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
Stat = null;
}
}
If (conn! = Null ){
Try {
Conn. close ();
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
Conn = null;
}
}

Let's look at a simple example:

Import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; public class FreedomJDBCDemo1 {public static void main (String [] args) {Connection conn = null; Statement stat = null; ResultSet rs = null; try {// 1. register the database Driver // -- because mysql registers once in the implementation of the Driver class, And we register again, therefore, when the MySql Driver is registered twice // -- when the MySql Driver object is created, the program and the specific Mysql Driver are bound together and need to be modified during database switching. Dynamic java code // DriverManager. registerDriver (new Driver (); Class. forName ("com. mysql. jdbc. driver "); // 2. obtain the database connection. Here we use a concise method to omit localhost and port conn = DriverManager. getConnection ("jdbc: mysql: // freedom? User = root & password = root "); // 3. obtains the transmitter object stat = conn. createStatement (); // 4. use the transmitter to transmit SQL statements to the database for execution and obtain the result set object rs = stat.exe cuteQuery ("select * from user"); // 5. retrieve the query result while (rs. next () {String name = rs. getString ("name"); System. out. println (name) ;}} catch (Exception e) {e. printStackTrace ();} finally {// 6. disable resource if (rs! = Null) {try {rs. close () ;}catch (SQLException e) {e. printStackTrace () ;}finally {rs = null ;}} if (stat! = Null) {try {stat. close () ;}catch (SQLException e) {e. printStackTrace () ;}finally {stat = null ;}} if (conn! = Null) {try {conn. close () ;}catch (SQLException e) {e. printStackTrace () ;}finally {conn = null ;}}}}}

We can encapsulate the above example to encapsulate common operations such as linking to a database and disabling resources as a tool class. We can adapt the path, user and password. In the future, you only need to modify the configuration file.

The configuration file is as follows:

Driver = com. mysql. jdbc. Driver
Url = jdbc: mysql: // freedom
User = root
Password = root

Tool class:

Import java. io. fileReader; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; import java. util. properties; public class JDBCUtils {private static Properties 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) ;}/ *** get Connection * @ throws ClassNotFoundException * @ throws SQLException */public static Connection getConn () throws ClassNotFoundException, SQLException {// 1. register the database Driver Class. forName (prop. getProperty ("driver"); // 2. get the connection return DriverManager. getConnection (prop. getProperty ("url"), prop. getProperty ("user"), prop. getProperty ("Password");}/*** close Connection */public static void close (ResultSet rs, Statement stat, Connection conn) {if (rs! = Null) {try {rs. close () ;}catch (SQLException e) {e. printStackTrace () ;}finally {rs = null ;}} if (stat! = Null) {try {stat. close () ;}catch (SQLException e) {e. printStackTrace () ;}finally {stat = null ;}} if (conn! = Null) {try {conn. close () ;}catch (SQLException e) {e. printStackTrace () ;}finally {conn = null ;}}}}

Let's look at the simplified operation example using the tool class:

Import java. SQL. connection; import java. SQL. resultSet; import java. SQL. statement; import org. junit. test; import com. itheima. util. JDBCUtils; public class FreedomJDBC {/*** @ Title: add * @ Description: add * @ throws */@ Testpublic void add () {Connection conn = null; statement stat = null; try {// 1. register the database driver // 2. get connection conn = JDBCUtils. getConn (); // 3. obtains the transmitter object stat = conn. createStatement (); // 4. execute the SQL statement int count = sta T.exe cuteUpdate ("insert into user values (null, 'Freedom ', '000000', 'Freedom @ qq.com', '2017-01-01 ')"); // 5. processing result if (count> 0) {System. out. println ("execution successful! The affected number of rows is "+ count);} else {System. out. println (" execution failed !! ") ;}} Catch (Exception e) {e. printStackTrace ();} finally {// 6. disable resources JDBCUtils. close (null, stat, conn) ;}/ *** @ Title: delete * @ Description: delete * @ throws */@ Testpublic void delete () {Connection conn = null; Statement stat = null; ResultSet rs = null; try {conn = JDBCUtils. getConn (); stat = conn.createstatement({{stat.exe cuteUpdate ("delete from user where name = 'freedom '");} catch (Exception e) {e. printStackTrace ();} finally {JDBCUtils. close (rs, stat, conn) ;}/ *** @ Title: update * @ Description: update * @ throws */@ Testpublic void update () {Connection conn = null; Statement stat = null; try {conn = JDBCUtils. getConn (); stat = conn.createStatement();stat.exe cuteUpdate ("update user set password = 666 where name = 'freedom '");} catch (Exception e) {e. printStackTrace ();} finally {JDBCUtils. close (null, stat, conn) ;}}/*** @ Title: find * @ Description: Query * @ throws */@ Testpublic void find () {Connection conn = null; Statement stat = null; ResultSet rs = null; try {conn = JDBCUtils. getConn (); stat = conn. createStatement (); rs = stat.exe cuteQuery ("select * from user where name = 'freedom '"); while (rs. next () {String name = rs. getString ("name"); String password = rs. getString ("password"); System. out. println (name + ":" + password) ;}} catch (Exception e) {e. printStackTrace ();} finally {JDBCUtils. close (rs, stat, conn );}}}

SQL injection attacks:
Since the SQL statements executed in dao are spliced, some of the content is transmitted by the user from the client, when the data imported by the user contains the SQL keyword, it is possible to use these keywords to change the semantics of SQL statements and execute some special operations. Such attacks are called SQL injection attacks.

PreparedStatement uses the pre-compilation mechanism to transmit the trunk and parameters of SQL statements to the database server separately, so that the database can identify the trunk and parameters of SQL statements, in this way, even if the parameter contains the SQL keyword, the database server only uses it as the parameter value, and the keyword does not work, thus preventing the problem of SQL Injection in principle.


PreparedStatement has the following three advantages:
~ 1. SQL Injection prevention
~ 2. Because the pre-compilation mechanism is used, the execution efficiency is higher than Statement.
~ 3. SQL statement usage? To replace the parameters, and then use the method to set them? The code is more elegant than the concatenated string.

Check the Code:

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import com.itheima.util.JDBCUtils;public class JDBCDemo3 {public static void main(String[] args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = JDBCUtils.getConn();ps = conn.prepareStatement("select * from user where name=? and password=?");ps.setString(1, "freedom");ps.setString(2, "666");rs = ps.executeQuery();while (rs.next()) {System.out.println(rs.getString("email"));}} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.close(rs, ps, conn);}}}

JDBC also supports big text and big binary processing, but it is rarely used in actual development. In general, you may need to modify the startup memory size of the virtual machine to process these big data. Here we will talk about the storage and reading of big binary data groups.

Import java. io. file; import java. io. fileInputStream; import java. io. fileOutputStream; import java. io. inputStream; import java. io. outputStream; import java. SQL. connection; import java. SQL. preparedStatement; import java. SQL. resultSet; import org. junit. test; import com. itheima. util. JDBCUtils;/** create a table in the database: create table blobdemo (id int primary key auto_increment, name varchar (100), content MEDIUMBLOB); */publ Ic class BlobDemo1 {@ Testpublic 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, "up to bytes"); File file = new File ("1.mp3"); ps. setBinaryStream (2, new FileInputStream (file), (int) file.length());ps.exe cuteUpdate ();} catch (Exception e) {e. printStackTrace ();} finally {JDBCUtils. close (rs, ps, conn) ;}@ Testpublic void findBlob () {Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try {conn = JDBCUtils. getConn (); ps = conn. prepareStatement ("select * From blobdemo "); rs = ps.exe cuteQuery (); 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 );}}}

Sometimes, when you need to send a batch of SQL statements to the database for execution, you should avoid sending them to the database one by one. Instead, you should adopt the JDBC batch processing mechanism to improve the execution efficiency. There are two methods for JDBC batch processing, each of which has its own advantages and disadvantages. First, let's look at the first method:

Import java. SQL. connection; import java. SQL. statement; import com. itheima. util. JDBCUtils;/* create database day10batch; use day10batch; create table batchDemo (id int primary key auto_increment, name varchar (20); insert into batchDemo values (null, 'aaa '); insert into batchDemo values (null, 'bbb'); insert into batchDemo values (null, 'cc'); insert into batchDemo values (null, 'd '); * // * batch processing in Statement mode: advantage: Multiple SQL statements with different structures can be executed. disadvantage: no pre-compilation mechanism is used, resulting in low efficiency, if you want to execute multiple SQL statements with the same structure but different parameters, you still need to write multiple SQL statement trunk */public class StatementBatch {public static void main (String [] args) {Connection conn = null; Statement stat = null; try {conn = JDBCUtils. getConn (); stat = conn. createStatement (); stat. addBatch ("create database day10batch"); stat. addBatch ("use day10batch"); stat. addBatch ("create table batchDemo (" + "id int primary key auto_increment," + "name varchar (20)" + ")"); stat. addBatch ("insert into batchDemo values (null, 'aaa')"); stat. addBatch ("insert into batchDemo values (null, 'bbb ')"); stat. addBatch ("insert into batchDemo values (null, 'cc')"); stat. addBatch ("insert into batchDemo values (null, 'D')" contains invalid stat.exe cuteBatch ();} catch (Exception e) {e. printStackTrace ();} finally {JDBCUtils. close (null, stat, conn );}}}

Let's look at the second method:

Import java. SQL. connection; import java. SQL. preparedStatement; import com. itheima. util. JDBCUtils;/* create table psbatch (id int primary key auto_increment, name varchar (30); * // * batch processing in the prparedStatement mode: advantage: a pre-compilation mechanism is available, high efficiency. when multiple SQL statements with the same structure and different parameters are executed, the SQL master does not need to be repeatedly written. Disadvantages: Only SQL statements with the same trunk and different parameters can be executed, there is no way to add a different SQL */public class PSBatch {public static void main (String [] args) {Connection conn = null; PreparedStatement ps = Null; try {conn = JDBCUtils. getConn (); ps = conn. prepareStatement ("insert into psbatch values (null ,?) "); For (int I = 1; I <= 100000; I ++) {ps. setString (1, "name" + I );ps.addBatch();if( I %1000==0){ps.executeBatch();ps.clearBatch();}}ps.exe cuteBatch ();} catch (Exception e) {e. printStackTrace ();} finally {JDBCUtils. close (null, ps, conn );}}}

Now, the basic usage of JDBC is explained here, and we hope to help people who see this article.