Basic usage of JDBC

JDBC (Java database Connection) is the database connectivity standard provided by Java, and the specific standard implementation is provided by each database vendor.

The following is an example of MySQL for a simple demonstration of the basic usage of the JDBC interface.

The basic steps of the JDBC Connection database are as follows:

1. Load the database driver to be connected, using the reflection mechanism.

The code to load the MySQL driver is: class.forname ("Com.mysql.jdbc.Driver");

2. Get a connection to the database to which you want to connect by DriverManager.

The code format is: Drivermanager.getconnection (c, "root", "1234");

Where string C is "Jdbc:mysql://localhost:3306/databasename?useunicode=true&characterencoding=utf8";

The following string specifies that it is possible to solve the problem of Chinese garbled, if the database default encoding supports Chinese can not be set.

The above two steps completed the connection of the database, if you want to further the operation of the database, according to different application scenarios to achieve.

1) Obtain and execute the SQL statement via statement.

Disadvantage: It is easy to cause SQL injection, which poses a security risk to the database. However, it is easy to use when performing SQL batch processing.

The following code can be used to demonstrate its usage.

Package Com.wxisme.jdbc01;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.ResultSet; Import java.sql.sqlexception;import java.sql.statement;/** * Connect MySQL basic process * @author Wxisme * */public class Demo02 {public static void Main (string[] args) {Connection con = null; Statement stmt = null; ResultSet rs = null;try {//Load Database driver Class.forName ("Com.mysql.jdbc.Driver");//build connection by driver Management string c = "jdbc:mysql:// Localhost:3306/student?useunicode=true&characterencoding=utf8 "; con = drivermanager.getconnection (c," root "," 1234 ");//create statement String sql =" SELECT * from student where b= ' Zhang San ' "; stmt = Con.createstatement ();//create result set not precompiled SQL can only Incoming SQLRS = stmt.executequery (sql) at execution time,//next () pointer to the next record, or return true if any, otherwise falsewhile (Rs.next ()) {for (int i=1; i<10; i+ +) {//getstring () can pass in the field name, or it can pass in the field's IndexSystem.out.print (rs.getstring (i) + "");} System.out.println ();}} catch (ClassNotFoundException | SQLException e) {e.printstacktrace ();} Finally {try {con.close ();} catch (SQLException e) {E.PRintstacktrace ();}            try {stmt.close ();} catch (SQLException e) {e.printstacktrace ();} try {rs.close ();} catch (SQLException e) {e.printstacktrace ();}}}}

2) Prevent SQL injection by preparestatement pre-compiling SQL.

Pros: You can prevent SQL injection and pre-compiling SQL to be more efficient and can be used? placeholders, which do not have to be spelled when writing SQL statements, and are not prone to errors. However, it is not easy to use in bulk processing.

public class Demo01 {public static void main (string[] args) {Connection con = null; PreparedStatement stmt = null; ResultSet rs = null;try {//Load Database driver Class.forName ("Com.mysql.jdbc.Driver");//build connection by driver Management string c = "jdbc:mysql:// Localhost:3306/student?useunicode=true&characterencoding=utf8 "; con = drivermanager.getconnection (c," root "," 1234 ");//create statement String sql =" SELECT * from student where b=? "; stmt = con.preparestatement (sql); stmt.setstring (1, "Zhang San");//Create result Set rs = Stmt.executequery (); while (Rs.next ()) {for (int i= 1; i<10; i++) {System.out.print (rs.getstring (i) + "");} System.out.println ();}} catch (ClassNotFoundException | SQLException e) {e.printstacktrace ();} Finally {try {con.close ();} catch (SQLException e) {e.printstacktrace ();}            try {stmt.close ();} catch (SQLException e) {e.printstacktrace ();} try {rs.close ();} catch (SQLException e) {e.printstacktrace ();}}}} 

3) Execute bulk SQL statements using batch processing once.

public class Demo03 {public static void main (string[] args) {Connection con = null; Statement stmt = null;try {//Load Database driver Class.forName ("Com.mysql.jdbc.Driver");//build connection by driver Management string c = "jdbc:mysql:// Localhost:3306/student?useunicode=true&characterencoding=utf8 "; con = drivermanager.getconnection (c," root "," 1234 "), Con.setautocommit (false);//Set the connected transaction to manual commit//Create statement  stmt = Con.createstatement (); for (int i=0; i< 900000; i++) {String sql = "INSERT into student (A,B,C) VALUES (' Chinese ', ' Liu Qi ', ' 2013010 ')"; Stmt.addbatch (sql);} Stmt.executebatch (); Con.commit ();//COMMIT TRANSACTION} catch (ClassNotFoundException | SQLException e) {e.printstacktrace ();} Finally {try {con.close ();} catch (SQLException e) {e.printstacktrace ();} try {stmt.close ();} catch (SQLException e) {e.printstacktrace ();}}}}

4) database transaction commit and rollback, by default, the database transaction is automatically committed, but if manually committed, in the last call to the database connection rollback method, after an SQL statement has an error throws an exception, the other SQL statements are no longer executed, the executed SQL statements are rolled back to the previous state, Is that if an exception is thrown, the data in the database does not change.

Package Com.wxisme.jdbc01;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.ResultSet; Import java.sql.sqlexception;import java.sql.preparedstatement;/** * Manual Commit and rollback of transactions * @author Wxisme * */public class Demo04 { public static void Main (string[] args) {Connection con = null; PreparedStatement stmt = null;try {//Load the database driver Class.forName ("Com.mysql.jdbc.Driver") first,//through driver management to establish the connection string c = "JDBC: Mysql://localhost:3306/student?useunicode=true&characterencoding=utf8 "; con = drivermanager.getconnection (c," Root "," 1234 ");//Create Statement Con.setautocommit (FALSE);//set to manually commit transaction String sql =" INSERT into student (?) VALUES (?) "; stmt = con.preparestatement (sql), stmt.setstring (1, "a"), Stmt.setstring (2, "Liu Qi"); Con.commit ();//manual COMMIT Transaction} catch ( ClassNotFoundException | SQLException e) {e.printstacktrace (); try {con.rollback ();//ROLLBACK TRANSACTION If an exception occurs} catch (SQLException E1) {e1.printstacktrace () ;}} Finally {try {con.close ();} catch (SQLException e) {e.printstacktrace ();} try {stmt.close ();} catch (SqlexceptiOn e) {e.printstacktrace ();} }}}

5) For date support, there are date,time,timestamp classes in the java.sql package to support time and date processing.

Package Com.wxisme.jdbc01;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import java.sql.sqlexception;import Java.sql.timestamp;import java.util.Random;/** * Time Processing Java.sql.date,time,timestamp * Insert records with date and timestamp type fields * @author Wxisme * */public class Demo05 {public static void main (STR Ing[] args) {Connection conn = null; PreparedStatement PS = null;try {//Load driver class Class.forName ("Com.mysql.jdbc.Driver"); String c = "Jdbc:mysql://localhost:3306/student?useunicode=true&characterencoding=utf8"; conn = Drivermanager.getconnection (c, "root", "1234"), for (int i=0;i<1000;i++) {PS = Conn.preparestatement ("INSERT INTO Student (Name,pswd,time,stime) VALUES (?,?,?,?) "); Ps.setobject (1, "tassel money" +i);p S.setobject (2, "123456"); int rand = 100000000+new Random (). Nextint (1000000000); Java.sql.Date Date = new Java.sql.Date (System.currenttimemillis ()-rand);  Timestamp stamp = new Timestamp (System.currenttimemillis ()-rand); If you need to insert a specified date, you can use Calendar, Dateformatps.setdate (3, DATe);p S.settimestamp (4, stamp);p s.execute ();}} catch (ClassNotFoundException e) {e.printstacktrace ();} catch (SQLException e) {e.printstacktrace ();} Finally{try {if (ps!=null) {ps.close ();}} catch (SQLException e) {e.printstacktrace ();} try {if (conn!=null) {conn.close ();}} catch (SQLException e) {e.printstacktrace ()}}}

 

Package Com.wxisme.jdbc01;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.sql.sqlexception;import Java.sql.Timestamp; Import Java.text.dateformat;import java.text.parseexception;import java.text.simpledateformat;/** * Time Processing Java.sql.date,time,timestamp * Find records for a time period * @author Wxisme * */public class Demo06 {/** * Convert the date represented by the string to a long number (format: yyyy-mm -DD hh:mm:ss) * @param datestr * @return */public static long str2date (String date) {DateFormat format = new Simpledatefor Mat ("Yyyy-mm-dd hh:mm:ss"); try {return format.parse (date). GetTime ();} catch (ParseException e) {e.printstacktrace (); return 0;}} public static void Main (string[] args) {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {//Load driver class Class.forName ("Com.mysql.jdbc.Driver"); String c = "Jdbc:mysql://localhost:3306/student?useunicode=true&characterencoding=utf8"; conn = Drivermanager.getconnection (c, "root", "1234");p s = Conn.preparestatemENT ("SELECT * from student where stime>?  and stime<? ORDER by stime "); Timestamp start = new Timestamp (str2date ("2015-5-1 8:10:20")); Timestamp end = new Timestamp (str2date ("2015-5-1 9:9:10"));p S.setobject (1, start);p s.setobject (2, end); rs = Ps.executequery (); while (Rs.next ()) {System.out.println (rs.getstring ("name") + "--" +rs.gettime ("time") + "--" + Rs.gettimestamp ("Stime"));}} catch (ClassNotFoundException e) {e.printstacktrace ();} catch (SQLException e) {e.printstacktrace ();} Finally{try {if (ps!=null) {ps.close ();}} catch (SQLException e) {e.printstacktrace ();} try {if (conn!=null) {conn.close ();}} catch (SQLException e) {e.printstacktrace ()}}}

Summary: JDBC database connection encapsulation is very good, the total steps are fixed, write a few demo after familiar with, but in the database connection to pay attention to a lot of details, the code to write the rigor of the bug.

Basic usage of JDBC