Getting Started with JDBC learning

I. JDBC Related CONCEPTS Introduction 1.1, Database driver

Here the concept of driving and usually hear the kind of driving concept is the same, such as the usual purchase of sound card, network card directly plugged into the computer is not available, you must install the appropriate driver before you can use the sound card and network card, the same reason, after we installed the database, Our application is also unable to use the database directly, must through the corresponding database driver, through the driver to deal with the database, as follows:

　　

1.2. Introduction to JDBC

In order to simplify and unify the operation of database, Sun Company defines a set of Java Operation Database Specification (interface), called JDBC. This interface is implemented by the database vendor, so that developers can manipulate the database by simply learning the JDBC interface and loading the specific driver through JDBC.

As shown in the following:

　　

JDBC is all called: Java data Base Connectivity (Java database connection), which consists primarily of interfaces.
The 2 packages that make up JDBC:
java.sql
Javax.sql
Developing a JDBC application requires support for the above 2 packages, as well as importing the appropriate JDBC database implementations (that is, database-driven).

Ii. writing JDBC Program 2.1, setting up the experiment environment

1. Create a library in MySQL and create data for the user table and the inserted table.

The SQL script is as follows:

1 CREATE database Jdbcstudy character set UTF8 collate utf8_general_ci; 2  3 use Jdbcstudy; 4  5 CREATE TABLE users (6     ID int primary KEY, 7     name varchar (), 8     password Varcha R (+), 9     email varchar (     birthday date11); insert into users (Id,name,password,email,birthday) values (1, ' Zhansan ', ' 123456 ', ' [email protected] ', ' 1980-12-04 '); INSERT into users (Id,name,password,email,birthday) VALUES (2, ' Lisi ', ' 123456 ', ' [email protected] ', ' 1981-12-04 '); INSERT into users (Id,name,password,email,birthday) VALUES (3, ' Wangwu ', ' 123456 ', ' [email protected] ', ' 1979-12-04 ');

2, create a new Java project, and import data-driven.
　　

3. Write the program to read the data from the user table and print it in the Command Line window.
The specific code is as follows:

1 package Me.gacl.demo; 2 Import java.sql.Connection; 3 Import Java.sql.DriverManager; 4 Import Java.sql.ResultSet; 5 Import java.sql.Statement; 6 7 public class Jdbcfirstdemo {8 9 public static void Main (string[] args) throws Exception {10//database to connect ur L11 string url = "Jdbc:mysql://localhost:3306/jdbcstudy"; 12//The user name that is used when connecting to the database is string username = "R Oot "; 14//The password that is used when connecting the database is password =" XDP "; 16 17//1. Load driver//drivermanager. Registerdriver (New Com.mysql.jdbc.Driver ()); This method is not recommended to load the driver 19 class.forname ("Com.mysql.jdbc.Driver"); It is recommended to use this method to load the driver20//2. Getting a link to a database 21Connection conn = drivermanager.getconnection (URL, username, password);22 23//3. Gets the statement24 used to send SQL statements to the databaseStatement st = Conn.createstatement ();27//4 String sql = "Select Id,name,password,email,birthday from users"; Send SQL to the database and get re that represents the result set Sultset28ResultSet rs = st.executequery (sql);29 30//5. Extract data from the result set (Rs.next ()) {System.out.println ("id=" + rs.getobject ("id") ): System.out.println ("name=" + rs.getobject ("name")), System.out.println ("password=" + rs.get Object ("password")), System.out.println ("email=" + rs.getobject ("email")), and System.out.println ( "Birthday=" + rs.getobject ("Birthday")); 37}38 39//6. Close the link and release the resource 40rs.close ();41st.close ();42conn.close ();43}44}

The results of the operation are as follows:

　　

2.2, DriverManager class explanation

The DriverManager in the JDBC program is used to load the driver and create a link to the database, a common method of this API:

1. Drivermanager.registerdriver (New Driver ())
2. Drivermanager.getconnection (URL, user, password),

Note: It is not recommended to use the Registerdriver method to register drivers in real -world development. There are two reasons:
1, see driver Source code can see, if in this way, will cause the driver to register two times, that is, in memory there will be two driver objects.
2, the program relies on the MySQL API, out of the MySQL jar package, the program will not compile, the future program to switch the underlying database will be very troublesome.

　　Recommended way:class.forname ("Com.mysql.jdbc.Driver");
This approach does not cause the drive object to recur in memory, and in this way, the program requires only a single string and does not rely on specific drivers to make the program more flexible.

2.3, the database URL explanation

The URL is used to identify the location of the database and tells the JDBC program which database to connect to by the URL address:

　　

Common database URL address notation:

• Oracle notation: Jdbc:oracle:thin: @localhost: 1521:sid
• SQL Server notation: jdbc:microsoft:sqlserver://localhost:1433; Databasename=sid
• MySQL notation: jdbc:mysql://localhost:3306/sid

If you are connecting to a local MySQL database, and the connection uses a port of 3306, the URL address can be abbreviated as: jdbc:mysql:///database

2.4, Connection class explanation

Connection in the JDBC program , which is used to represent the link of the database, collection is the most important object in the database programming, the client and the database all interaction is done through the Connection object, the common method of this object:

• Createstatement (): Creates a statement object that sends SQL to the database.
• Preparestatement (SQL): Creates a Preparesatement object that sends precompiled SQL to the database.
• Preparecall (SQL): Creates the CallableStatement object that executes the stored procedure.
• Setautocommit (Boolean autocommit): Sets whether the transaction is automatically committed.
• Commit (): commits the transaction on the link.
• Rollback (): Rolls back the transaction on this link.

2.5, Statement class explanation

The statement object in the JDBC program is used to send SQL statements to the database, statement object common methods:

• 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
• Addbatch (String sql): puts multiple SQL statements into one batch.
• ExecuteBatch (): Sends a batch of SQL statement execution to the database.

2.6, ResultSet class explanation

The resultset in the JDBC program is used to represent the execution result of the SQL statement. A table-like approach is used when the resultset encapsulates the execution result. The ResultSet object maintains a cursor to the table data row, and initially, the cursor calls the Resultset.next () method before the first row, allowing the cursor to point to a specific row of data and invoke the method to fetch the row's data.

Using JDBC to make a database crud One, statement object introduction

The statement object in JDBC is used to send SQL statements to the database, and to complete the additions and deletions of the database, it is only necessary to send additions and deletions to the database through this object.
The Executeupdate method of the statement object is used to send an increment, delete, and change SQL statement to the database , and an integer is returned when the executeupdate finishes executing ( That is, adding and deleting the statement resulted in the database a few rows of data changed.
The Statement. ExecuteQuery method is used to send a query statement to the database, and the ExecuteQuery method returns the ResultSet object that represents the query result .

1.1. CRUD Operation-create

Use the executeupdate (String sql) method to complete the data addition operation, sample actions:

int num = st.executeupdate (sql); 4 if (num>0) {5     System.out.println ("Insert succeeded!!! "); 6}

1.2. CRUD Operation-update

Use the executeupdate (String sql) method to complete data modification operations, sample actions:

int num = st.executeupdate (sql); 4 if (num>0) {5     System.out.println ("Modified successfully!!! "); 6}

1.3. CRUD Operation-delete

Use the executeupdate (String sql) method to complete the data delete operation, sample actions:

1 Statement st = Conn.createstatement (); 2 String sql = "Delete from user where id=1; 3 int num = st.executeupdate (SQL); 4 if (num>0) {5     System.out.println ("Delete succeeded!!! "); 6}

1.4. CRUD Operation-read

Use the executequery (String sql) method to complete the data query operation, sample actions:

1 Statement st = Conn.createstatement (); 2 String sql = "SELECT * from user where id=1; 3 ResultSet rs = st.executeupdate (SQL), 4 while (Rs.next ()) {5     //Depending on the data type of the fetched column, the corresponding method of calling RS is mapped to the Java object 6}

Second, the use of JDBC database additions and Deletions 2.1, set up the experimental environment

1. Create a library in MySQL and create data for the user table and the inserted table.

The SQL script is as follows:

1 CREATE DATABASE Jdbcstudy; 2  3 use Jdbcstudy; 4  5 CREATE TABLE users (6     ID int primary KEY, 7     name varchar (), 8     password Varcha R (+), 9     email varchar (     birthday date11);

2, create a new Javaweb project, and import MySQL database driver.

　　

3. Create a db.properties file in the SRC directory, as shown in:

　　

Write the connection information for the MySQL database in Db.properties, as shown in the following code:

1 driver=com.mysql.jdbc.driver2 url=jdbc:mysql://localhost:3306/jdbcstudy3 Username=root4 Password=XDP

4, write a Jdbcutils tool class, used to connect the database, get the database connection and release the database connection, the code is as follows:

 1 package me.gacl.utils; 2 3 Import Java.io.InputStream; 4 Import java.sql.Connection; 5 Import Java.sql.DriverManager; 6 Import Java.sql.ResultSet; 7 Import java.sql.SQLException; 8 Import java.sql.Statement; 9 Import java.util.properties;10 One public class Jdbcutils {A private static String Driver = null;14 private S Tatic string url = null;15 private static string username = null;16 private static string password = null;17 1 8 static{19 try{20//Read the database connection information in the Db.properties file InputStream in = JdbcUtils.class.get ClassLoader (). getResourceAsStream ("Db.properties"); properties prop = new properties (); PROP.L Oad (in); 24 25//Get database connection Driver Driver = prop.getproperty ("Driver"); 27//Get Database Connection URL address-url = prop.getproperty ("url"); 29//Get database connection user name: Username = Prop.getproperty ( "username"); 31//Get database connection password + pasSword = prop.getproperty ("password"); 33 34//LOAD Database driver Class.forName (driver); 36     PNS}catch (Exception e) {}41 throw new Exceptionininitializererror (e); 39}40 /**43 * @Method: GetConnection44 * @Description: Get database Connection Object * @Anthor: Aloof Wolf *47 * @return Con Nection database Connection Object * @throws SQLException49 * */public static Connection getconnection () throws Sqlexception{5     1 return drivermanager.getconnection (URL, username,password),}53/**55 * @Method: release56 * @Description: Free resources, 57 * The resources to be freed include the connection database connection object, the statement object responsible for executing the SQL command, and the ResultSet object that stores the query result * @Anthor: Orphan *60 * @param conn61 * @param st62 * @param rs63 * * * public static void release (Connection                  Conn,statement St,resultset RS) {$ if (rs!=null) {try{67//Close the ResultSet object that stores the query results68rs.close ();}catch (Exception e) {e.printstacktrace ();}72 rs = null;73 }74 if (st!=null) {try{76//Close the statement object that is responsible for executing the SQL command77st.close ();}catch (Exception e) {e.printstacktrace (); 80}81}82 83 if (conn!=null) {try{85//Close Connection database connection object86conn.close ();}catch (Exception e) {e.printstacktrace (); 89}90}91}92}

2.2. Use the Statement object to complete CRUD operations on the database

The test code is as follows:

  1 package Me.gacl.demo;  2 3 Import java.sql.Connection;  4 Import Java.sql.ResultSet;  5 Import java.sql.Statement;  6 Import Me.gacl.utils.JdbcUtils;  7 8 Import Org.junit.Test; 9/** * @ClassName: Jdbccrudbystatement * @Description: Complete CRUD operations on the database through statement objects * @author: Aloof from the wolves * @date         : 2014-9-15 11:22:12 * * * * * * * public class Jdbccrudbystatement {@Test-public void Insert () {21 Connection conn = null; Statement st = null; ResultSet rs = null; try{25//Get a database connection of conn = Jdbcutils.getconnection (); 27//through the Conn object for the The statement object of the row SQL command is St = Conn.createstatement (); 29//SQL command to execute in String sql = "INSERT into users (Id,name,password,email,birthday) VALUES (3, ' White Tiger King ') , ' 123 ', ' [email protected] ', ' 1980-09-09 '); 31//Perform the insert operation, the Executeupdate method returns the number of successful bars, int num = st.executeupdate (SQL); if (NUM≫0) {System.out.println ("Insert success!! "); }catch (Exception e) {e.printstacktrace ();}finally {jdbcutils.release (conn, St, RS) and release related resources after completion of execution of//sql Est. public void Delete () {Connection conn = null; Statement st = null; ResultSet r s = null; try{Wuyi conn = Jdbcutils.getconnection (); String sql = "Delete from users where id=3 "; The St = Conn.createstatement (); A. int num = st.executeupdate (SQL); if (num>0) {System.out.println ("Delete succeeded!! "); }catch (Exception e) {e.printstacktrace ();}finally {jdbcutils.release (conn, St, RS), + (+)}, @Test Connection conn = nUll Statement st = null; ResultSet rs = null; try{conn = Jdbcutils.getconnection (); String sql = "Update users set name= ' aloof wolf ' , email= ' [email protected] ' where id=3 '; A. St = Conn.createstatement (); A. int num = st.executeupdate (SQL); if (num>0) {System.out.println ("Update successful!! "); }catch (Exception e) {e.printstacktrace (); Bayi}finally          {jdbcutils.release (conn, St, RS); (+)--------@Test, public void Find () {89 Connection conn = null; Statement st = null; ResultSet rs = null; try{conn = jdbcutils.getconnection () 94 String sql = "SELECT * from users where ID =3 "; The St = Conn.createstatement (); rs = st.executequery (SQL);        if (Rs.next ()) {98         System.out.println (rs.getstring ("name"));             }100}catch (Exception e) {101 e.printstacktrace (); 102}finally{103 Jdbcutils.release (Conn, St, RS); 104}105}106}

Three, PreparedStatement object introduction

　　Preperedstatement is a subclass of statement , and its instance object can be obtained by calling the Connection.preparedstatement () method, As opposed to statement objects: Preperedstatement can avoid problems with SQL injection .
Statement causes the database to compile SQL frequently, potentially causing a database buffer overflow. PreparedStatement can pre-compile SQL to improve the efficiency of database execution . and preperedstatement for parameters in SQL, allowing for substitution in the form of placeholders, simplifying the writing of SQL statements .

3.1.using PreparedStatement objects to complete CRUD operations on a database

The test code is as follows:

1 package Me.gacl.demo;  2 3 Import java.sql.Connection;  4 Import java.sql.PreparedStatement;  5 Import Java.sql.ResultSet;  6 Import Java.util.Date;  7 Import Me.gacl.utils.JdbcUtils;  8 Import Org.junit.Test;  9/** * @ClassName: Jdbccrudbypreparedstatement * @Description: Complete CRUD operations on the database through PreparedStatement objects * @author: Lonely Wolf * @date: 2014-9-15 11:21:42 * * * * * * public class Jdbccrudbypreparedstatement {@Test p ublic void Insert () {Connection conn = null; 22PreparedStatement St= NULL; ResultSet rs = null; try{25//Get a database connection conn = Jdbcutils.getconnection (); 27//SQL command to execute, SQL The parameters in use? As a placeholder for String sql = "INSERT into users (Id,name,password,email,birthday) VALUES (?,?,?,?,?)"; 29//Gets the Preparestatement object responsible for executing the SQL command through the Conn object 30  st = conn.preparestatement (SQL);    31//Assign values to the parameters in the SQL statement, note that the index is starting from 1/** * The type of each field in the SQL statement is as follows: * +----- -----+-------------+ 35 | Field | Type | +----------+-------------+ 37 | ID | Int (11) | 38 | name | varchar (40) | 39 | password | varchar (40) | 40 | email | varchar (60) | 41 | Birthday | Date |             +----------+-------------+ * * St.setint (1, 1);//id is an int type of 45             St.setstring (2, "White Tiger God Emperor");//name is varchar (string type) st.setstring (3, "123");//password is varchar (string type) 47 St.setstring (4, "[email protected]");//email is varchar (string type) St.setdate (5, New java.sql.Date (New Da Te (). GetTime ());//birthday is a date type 49//performs an insert operation, the Executeupdate method returns the number of successful numbers int num = St.executeupdat E ();           Wuyi if (num>0) {52      System.out.println ("Insert success!! "); }catch (Exception e) {e.printstacktrace ();}finally {The relevant resources are released after completion of//sql execution Jdbcutils.release (conn, St, RS); Est. public void Delete () {Connection conn = null; PreparedStatement st = null; Ultset rs = null; try{conn = Jdbcutils.getconnection (); String sql = "Delete from users where id=? "; Conn.preparestatement st = (SQL); St.setint (1, 1); A. int num = St.executeupdate (); if (num>0) {System.out.println ("Delete succeeded!! ");             }catch (Exception e) {e.printstacktrace ();}finally{80 Jdbcutils.release (Conn, St, RS); Bayi} [Connection] () ()nn = null; PreparedStatement st = null; ResultSet rs = null; try{conn = Jdbcutils.getconnection (); the String sql = "Update users set Name=?,emai L=? where id=? "; The conn.preparestatement st = SQL; St.setstring (1, "GaCl"); 94 St.setstring (2, "[email protected]"); St.setint (3, 2); # int num = St.executeupdate (); if (num>0) {98 System.out.println ("Update succeeded!! "); }100}catch (Exception e) {101 e.printstacktrace (); 102 103}finally          {104 Jdbcutils.release (conn, St, RS);}106}107 108 @Test109 public void Find () {110 Connection conn = null;111 PreparedStatement st = null;112 ResultSet rs = null;113 try{11 4 conn = Jdbcutils.getconnection (); String sql = "SELECT * from Users where id=?"; 116th = conn.preparestatement (SQL); 117th.setint (1, 1); 118 rs = St.executequery (); 119 if (Rs.next ()) {System.out.println (rs.getstring ("name")); 121}122}catch (Exception e) {123 124}finally{125 jdbcutils.release (conn, St, RS); 126}127}128}

The above is a simple summary of the database crud using JDBC.

Getting Started with JDBC learning