SQL Learning Notes Database special topic (iv): A brief talk on JDBC usage

The jar package provided by the database vendor to manipulate the database is database driven. If individual vendors provide their own database drivers that can lead to a high cost of learning for developers, Sun provides a set of interface specifications that database drivers should follow, called JDBC, which is inherently a lot of interfaces. in short, JDBC is a set of interface specifications for manipulating databases, and since all database drivers follow the JDBC specification, we learn and use the interfaces in JDBC as long as we are learning and using the database.

2 packages that make up JDBC:Java.sql,javax.sql, the development of a JDBC application requires the support of the above 2 packages, as well as the need to import the appropriate JDBC database implementations (that is, database-driven).

Let's take a look at the steps to use JDCB:

* Create a good table in the database
* Import the database driver package in the program
1. Registering the database driver
Drivermanager.registerdriver (New Driver ());//Disadvantage One: observing Mysqldriver source found this method caused the database driver to be registered two times. Disadvantage two: The entire program domain MySQL database driver binding increases the coupling
Class.forName ("Com.mysql.jdbc.Driver");
2. Get the connection
Drivermanager.getconnection (URL, user, password);
~url's wording:
Oracle notation: Jdbc:oracle:thin: @localhost: 1521:sid
sqlserver-jdbc:microsoft:sqlserver://localhost:1433; Databasename=sid
Mysql-jdbc:mysql://localhost:3306/sid
~url parameters that can be connected
User, password
Useunicode=true&characterencoding=utf-8


3. Get the transmitter
Createstatement (): Creates a statement object that sends SQL to the database.
Preparestatement (SQL): Creates a Preparesatement object that sends precompiled SQL to the database.
4. Using the transmitter execution SQL statement to get 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. Traverse the result set to remove the structure
ResultSet saves the query results in memory in the form of a table, which also maintains a cursor, at the beginning of the cursor before the first row, each time the next () method is called to try to move down one row, if the move successfully returns true;
ResultSet also provides a number of get methods to get different types of data in query results
In addition to the next method, the following methods can be used to traverse the result set:
Next (): Move to the next line
Previous (): Move to previous line
Absolute (int row): Move to the specified line
Beforefirst (): Moves the front of the resultset.
Afterlast (): Moves to the last face of the resultset.
6. Releasing Resources
Conn is a limited resource that runs out of the table immediately to release
Stat takes up memory, so you have to release it when you're done.
RS takes up memory, so it needs to be released when it's finished.
Released when created after the first 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. Registering a database driver//--because MySQL registered once in the implementation of the driver class, and we registered once again, it causes the MySQL driver to be registered two times//-- When you create a MySQL Driver object, it causes the program and the specific MySQL driver to be tied together, and you need to change the Java code//drivermanager.registerdriver (new Driver ()) when switching the database; Class.forName ("Com.mysql.jdbc.Driver");//2. Get the database connection, use a concise notation here, you can omit the localhost and port conn = Drivermanager.getconnection ( "Jdbc:mysql:///freedom?user=root&password=root");//3. Gets the transmitter object stat = Conn.createstatement ();//4. Use the transfer SQL statement from the transmitter to execute in the database, get the result set Object rs = Stat.executequery ("SELECT * from user");//5. Traversal result set Get query result while (Rs.next ()) {String name = Rs.getstring ("name"); SYSTEM.OUT.PRINTLN (name);}} catch (Exception e) {e.printstacktrace ();} FINALLY{//6. Close 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 by encapsulating common parts such as linked databases, shutting down resources, and encapsulating them as a tool class. We can use the path, user and password to fit well. You only need to modify the configuration file later.

The contents of the configuration file are as follows:

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

Look at the 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 ();p rop.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 CLASSNOTF Oundexception, 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 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 operating example of 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 database driver//2. Get Connection conn = Jdbcutils.getconn ();//3. Get the Transmitter object stat = Conn.createstatement ();//4. Execute SQL statement int count = stat.executeupdate ("INSERT INTO user values" (NULL, ' freedom ', ' 123456 ', ' [email protected] ', ' 2012-01-01 ');//5. Processing results if (Count > 0) {System.out.println ("execution succeeded!") (the number of rows affected is "+ count");} else {System.out.println ("Execution failed!!!" );}} catch (Exception e) {e.printstacktrace ();} finally {//6. Close resource Jdbcutils.close (NULL, STAT, conn);}} /** * @Title: Delete * @Description: Remove * @throws */@Testpublic void Delete () {Connection conn = null; Statement stat = null; ResultSet rs = null;try {conn = Jdbcutils.getconn (); stat = Conn.createstatement (); Stat.executeupdate ("Delete from user wh ere name= ' freedom ');} catch (Exceptione) {e.printstacktrace ();} finally {Jdbcutils.close (RS, stat, conn);}} /** * @Title: Update * @Description: Updates * @throws */@Testpublic void update () {Connection conn = null; Statement stat = null;try {conn = Jdbcutils.getconn (); stat = Conn.createstatement (); Stat.executeupdate ("Update user set P assword=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.executequery ("SELECT * from us Er 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:
Because the SQL statements executed in DAO are stitched up, and some of them are passed in by the user from the client, it is possible to change the semantics of the SQL statement by using these keywords when the user passes in the data that contains the SQL keyword, thus performing some special operations. This attack is called a SQL injection attack.

PreparedStatement uses a pre-compiled mechanism to transfer the backbone and parameters of the SQL statement to the database server separately, so that the database can distinguish which is the backbone of the SQL statement which is the parameters, so even if the parameters with the SQL keyword, The database server simply uses him as a parameter value, and the keyword doesn't work, preventing SQL injection from being a principle


PreparedStatement mainly has the following three advantages:
To prevent SQL injection
The efficiency of the execution is higher than statement due to the use of the precompilation mechanism.
The ~3.sql statement uses the? form instead of the parameter, and then sets the value of the method, which is more elegant than the stitching string.

See Using 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 ();p s = conn.preparestatement ("SELECT * from user where name=? and password=? "); Ps.setstring (1, "freedom");p s.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 large text and binary processing, but we rarely use it in real-world development, and in general, processing these big data may also require modifying the boot memory size of the virtual machine. Let's talk about the storage and reading of the binary data set.

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 creation table Blobdemo (ID int primary KEY auto_inc Rement, Name varchar (+), content Mediumblob); */public class BlobDemo1 {@Testpublic void Addblob () {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {conn = Jdbcutils.getconn ();p s = conn.preparestatement ("INSERT into Blobdemo values (null,?,?)"); Ps.setstring (1, "Gundam. mp3"); File File = new file ("1.mp3");p S.setbinarystream (2, new FileInputStream (file), (int) file.length ());p s.executeupdate () ;} 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 ();p s = Conn.preparEstatement ("SELECT * from Blobdemo"), rs = Ps.executequery (), while (Rs.next ()) {String name = rs.getstring ("name"); Nputstream in = Rs.getbinarystream ("content"), outputstream out = new FileOutputStream (name); byte[] bs = new byte[1024];in t 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 execution, you should avoid sending execution to the database, instead, use the JDBC batch mechanism to improve execution efficiency. There are two ways to batch processing of JDBC, each with its own advantages and disadvantages. We'll look at the first way:

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), insert into Batchdemo values (n ull, ' aaaa '); insert into batchdemo values (null, ' BBB '); Insert to Batchdemo values (null, ' CC '); INSERT INTO Batchdemo VALUES (NULL, ' d '); *//*statement: Pros: You can execute multiple SQL statements with different structures disadvantage: without the use of precompiled mechanism, inefficient, if you want to execute more than one structure with the same parameters only different SQL, still need to write multiple SQL statements of the backbone */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 (+) ")"), Stat.addbatch ("INSERT into batchdemo values (null, ' AAAA ')"); Stat.addbatch (" INSERT into Batchdemo values (null, ' BBB ') ") Stat.addbatch (" INSERT into batchdemo values (null, ' CC ') "); stat.aDdbatch ("INSERT into batchdemo values (null, ' d ')"); Stat.executebatch ();} catch (Exception e) {e.printstacktrace ();} Finally{jdbcutils.close (NULL, STAT, conn);}}}

Then look at the second way:

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)); *//*prparedstatement implementation of batch processing: The advantages: There is a pre-compilation mechanism, high efficiency. When you execute multiple structures that have the same parameters, you do not need to repeat the skeleton of sql: You can only execute SQL with different backbone parameters There is no way to add a struct in a batch with different SQL */public class Psbatch {public static void main (string[] args) {Connection conn = null; PreparedStatement PS = Null;try{conn = Jdbcutils.getconn ();p s = conn.preparestatement ("INSERT into psbatch values (NULL,?) "); for (int i=1;i<=100000;i++) {ps.setstring (1," name "+i);p s.addbatch (); if (i%1000==0) {ps.executebatch (); Ps.clearbatch ();}} Ps.executebatch ();} catch (Exception e) {e.printstacktrace ();} Finally{jdbcutils.close (NULL, PS, conn);}}}

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

SQL Learning Notes Database special topic (iv): A brief talk on JDBC usage