Javaweb Learning Summary (33)--using JDBC to crud a database

Javaweb Learning Summary (33)--using JDBC to make crud One, statement object introduction to Database

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 after the execution of the executeupdate, an integer is returned (that is, the additions and deletions result in several rows of data in the database).
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:

1 Statement st = Conn.createstatement (); 2 String sql = "INSERT into user (...) VALUES (...)"; 3 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:

1 Statement st = Conn.createstatement (); 2 String sql = "Update user set Name= ' where name= '"; 3 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 results 68 Rs.close ();}catch (Exception e) {e.printstacktrace ();}72 rs = null;73 }74 if (st!=null) {try{76//Close the statement object responsible for executing the SQL command St.close (         );}catch (Exception e) {e.printstacktrace (); 80}81}82 83             if (conn!=null) {try{85//Close Connection Database Connection object Conn.close (); 87 }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. Use the PreparedStatement 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.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; PreparedStatement 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, the ST = conn.preparestatement (SQL);         31//Assign values to the parameters in the SQL statement, note that the index is 1-based 32/** 33     * 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) {System.out.println ("Insert success!! ");  53} 54 55       }catch (Exception e) {e.printstacktrace ();//sql}finally{After the completion of the implementation of the relevant resources released 59 Jdbcutils.release (Conn, St, RS); (): $ Connection}, @Test public void Delete () {conn = null; Prep Aredstatement st = null; ResultSet 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) (): () () () () () {conn = null; Prep Aredstatement 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=?"; Conn.preparestatement (SQL); 117 St.setint (1, 1); 118 rs = St.executequery (); 119 if (Rs.next ()) {+ SYSTEM.OUT.PRINTLN (rs.getstr ing ("name")); 121}122}catch (Exception e) {123 124}finally{125 JDBCU Tils.release (Conn, St, RS); 126}127}128}

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

Javaweb Learning Summary (33)--using JDBC to crud a database