Javaweb's JDBC

First, Introduction

C # defines the ADO interface to enable access to databases such as SQL Server, Oracel, and Java, which defines the JDBC interface for database access, and the Java language can access the database as long as the database provider implements those interfaces.

Second, the work flow

1. Drive Introduction

Database provider Implementation of the JDBC interface, a few databases, Java know what kind of, so we have to register a different driver to operate the corresponding database, the registration driver has to have a driver is, so the first is to introduce the driver into the project, here with MySQL example, before also used in C # Operation of the MySQL database, today with Java operation, Drive: https://dev.mysql.com/downloads/file/?id=468319, the downloaded files extracted, Will find the Mysql-connector-java-5.1.41-bin.jar file, put the file into the Web-info/lib, this completes the introduction of the driver.

2. Registered Driver

After the introduction of the driver Java also do not know what database is used, so you have to register to know who, after registration will return to the corresponding driver management objects, and into the same position, you went to the company but do not report that also do not know to come, report to the individual process.

3. Create a connection

The database may be stored remotely, so how to catch up with the database, which requires a connection.

4. Perform operation

Connected after what to do, not always connected to do things ah, so after the connection to perform database operations, additions and deletions and other changes.

5. Return results

Add and remove changes to find out, always have an answer, or how to know success or not, query will return the query data, increase, delete, modify will return the number of rows affected.

6. Release

Return the results, but can not be attached to the database Ah, so also occupy resources, created objects are not released, also occupy space, so use it off.

Above roughly the operation of the database process listed below, the following through the experiment to operate.

7. Verification

This first creates a database TestDB in the local database, and then creates a users table in the table with the ID, name, age, Birthday fields representing different data types.

<% @pageImport= "Com.mysql.jdbc.Driver"%><%@ page language= "java" contenttype= "text/html; Charset=utf-8 "pageencoding= "UTF-8"%><%@ pageImport= "java.sql.*"%><%@ pageImport= "Java.util.*"%><! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" "Http://www.w3.org/TR/html4/loose.dtd" >Connection Conn=NULL; Statement stmt=NULL; ResultSet RS=NULL; Try{       //Registration DriverDrivermanager.registerdriver (Newcom.mysql.jdbc.Driver ()); //get the Connection object by registering the driver connectionConn=drivermanager.getconnection ("Jdbc:mysql://localhost:3306/testdb", "root", "123456"); //returns the result by performing an operation on the statement object ResultSetstmt=conn.createstatement (); //return ResultsRs=stmt.executequery ("SELECT * from Users");  while(Rs.next ()) {out.println ("Name:" +rs.getstring ("name") + "Age:" +rs.getint ("ages") + "date of birth:" +rs.getdate ("Birthday")); }}Catch(SQLException e) {out.println (E.getmessage ()); E.printstacktrace ();}finally{    //Freeing Resources    if(conn!=NULL) {conn.close (); }    if(stmt!=NULL) {stmt.close (); }    if(rs!=NULL) {rs.close (); }}  %></body>View Code
Ii. SQL execution with parameters
The above uses statement to query the database, but statement can not use the SQL with parameters, if the direct splicing of SQL statements without parameters there is a risk of injection attacks, if you want to use the parameters of SQL need to use its child class object: PreparedStatement. Use it to prevent injection attacks.
<% @pageImport= "Com.mysql.jdbc.Driver"%><%@ page language= "java" contenttype= "text/html; Charset=utf-8 "pageencoding= "UTF-8"%><%@ pageImport= "java.sql.*"%><%@ pageImport= "Java.util.*"%><! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" "Http://www.w3.org/TR/html4/loose.dtd" >Connection Conn=NULL; ResultSet RS=NULL; PreparedStatement prestmt=NULL; Try{       //Registration DriverDrivermanager.registerdriver (Newcom.mysql.jdbc.Driver ()); //get the Connection object by registering the driver connectionConn=drivermanager.getconnection ("Jdbc:mysql://localhost:3306/testdb", "root", "123456"); String SQL= "INSERT into users (Name,age,birthday) VALUES (?,?,?)"; Prestmt=conn.preparestatement (Sql,statement.return_generated_keys); Prestmt.setstring (1, "Cuiyw"); Prestmt.setint (2, 25); Prestmt.setdate (3,NewJava.sql.Date (Newjava.util.Date (). GetTime ()); intresult=prestmt.executeupdate (); if(result>0) {out.println ("New Success"); RS=Prestmt.getgeneratedkeys ();  while(Rs.next ()) {out.println ("The generated primary key ID is:" +rs.getint (1)); }    } }Catch(SQLException e) {out.println (E.getmessage ()); E.printstacktrace ();}finally{    //Freeing Resources    if(conn!=NULL) {conn.close (); }    if(prestmt!=NULL) {prestmt.close (); }    if(rs!=NULL) {rs.close (); }}  %></body>View Code
The above code implements two functions, one by inserting a record into the Users table, using SQL with the PreparedStatement parameter, and two getting the ID of the autogrow column. Ado. NET uses the @@ identity, Getgeneratedkeys () is used in JDBC, but takes statement.return_generated_ when instantiating PreparedStatement KEYS.
Third, batch processing
Statement, PreparedStatement can execute new, modify, and delete multiple SQL statements at once. General batch and transaction use, such as transfer. The following is the transaction batch insert data.
<% @pageImport= "Com.mysql.jdbc.Driver"%><%@ page language= "java" contenttype= "text/html; Charset=utf-8 "pageencoding= "UTF-8"%><%@ pageImport= "java.sql.*"%><%@ pageImport= "Java.util.*"%><! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" "Http://www.w3.org/TR/html4/loose.dtd" >Connection Conn=NULL; ResultSet RS=NULL; PreparedStatement prestmt=NULL; Try{       //Registration DriverDrivermanager.registerdriver (Newcom.mysql.jdbc.Driver ()); //get the Connection object by registering the driver connectionConn=drivermanager.getconnection ("Jdbc:mysql://localhost:3306/testdb?rewritebatchedstatements=true", "Root", " 123456 "); String SQL= "INSERT into users (Name,age,birthday) VALUES (?,?,?)"; //Open TransactionConn.setautocommit (false); Prestmt=conn.preparestatement (SQL);  for(inti=0;i<2;i++) {prestmt.setstring (1, "Cuiyw" +i); Prestmt.setint (2, 25+i); Prestmt.setdate (3,NewJava.sql.Date (Newjava.util.Date (). GetTime ());    Prestmt.addbatch (); }    //Batch processingPrestmt.executebatch (); //Commit a transactionConn.commit (); Conn.setautocommit (true);}Catch(SQLException e) {//rolling backConn.rollback ();    Out.println (E.getmessage ()); E.printstacktrace ();}finally{    //Freeing Resources    if(conn!=NULL) {conn.close (); }    if(prestmt!=NULL) {prestmt.close (); }    if(rs!=NULL) {rs.close (); }}  %></body>View Code
Iv. calling a stored procedure
During the use of the database, stored procedures may be called, and stored procedures can be invoked using CallableStatement.
Call storage function 1. {? = call <procedure-name>[(<ARG1>,<ARG2>,. ...)]}
Call stored Procedure 2. {Call <procedure-name>[(<ARG1>,<ARG2>,. ...)]}
Registering an out parameter with the Registeroutparameter () method of the CallableStatement object
The in or out parameter is set by the Setxxx () method of the CallableStatement object, and if you want to set the parameter to NULL, you can use the SetNull ()
If you are calling a stored procedure with a return parameter, you also need to get the output parameters through the getxxx () of the CallableStatement object
First, a stored procedure is created in the database with one input parameter and one output parameter.
<% @pageImport= "Com.mysql.jdbc.Driver"%><%@ page language= "java" contenttype= "text/html; Charset=utf-8 "pageencoding= "UTF-8"%><%@ pageImport= "java.sql.*"%><%@ pageImport= "Java.util.*"%><! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" "Http://www.w3.org/TR/html4/loose.dtd" >Connection Conn=NULL; ResultSet RS=NULL; CallableStatement callstmt=NULL; Try{       //Registration DriverDrivermanager.registerdriver (Newcom.mysql.jdbc.Driver ()); //get the Connection object by registering the driver connectionConn=drivermanager.getconnection ("Jdbc:mysql://localhost:3306/testdb?rewritebatchedstatements=true", "Root", " 123456 "); Callstmt=conn.preparecall ("{Call Selectuserbyid (?,?)}"); Callstmt.setint (1, 2); Callstmt.registeroutparameter (2, Types.integer); RS=Callstmt.executequery ();  while(Rs.next ()) {out.println ("Name:" +rs.getstring ("name") + "Age:" +rs.getint ("ages") + "date of birth:" +rs.getdate ("Birthday")); } out.println ("Stored Procedure return value:" +callstmt.getint (2));}Catch(SQLException e) {out.println (E.getmessage ()); E.printstacktrace ();}finally{    //Freeing Resources    if(conn!=NULL) {conn.close (); }    if(callstmt!=NULL) {callstmt.close (); }    if(rs!=NULL) {rs.close (); }}  %></body>View Code
V. Other
For JDBC operation binary data, compared to slices, meta data, connection pool is not introduced, in the development if the direct use of JDBC, these will also be encapsulated into a class, now the project also generally use the framework to map the database operation.
Javaweb's JDBC