First, JDBC overview (MySQL)
1. Java DataBase Connective
1) Concept: JDBC is actually a set of Java Operating database specification ( interface );
2) database driver:
Each database vendor to let the Java language to operate the database must implement this interface, each database is written a set of implementation class called database driver.
3) Use:
Import MySQL Database jar package
Place the jar in the Lib directory right-click Add--->builderpath
Load Driver
Class.forName ("Com.mysql.jdbc.Driver");
Get a database Connection object
String url = "jdbc:mysql://localhost:3306/mydb_01";//Master Protocol: Sub-Protocol://IP: Port number/database name
String user = "root";
String password = "root";
Connection conn = drivermanager.getconnection (URL, user, password);
Get Action Object
Statement stat = conn.createstatement ();
Execute sequel statement
String sql = "SQL additions and deletions change the statement";
ResultSet set = Stat.executequery (SQL); // Check Returns a result set
Set.next () set.getstring ("username");
int i = stat.executeupdate (sql); //increment, delete, change the number of rows affected by the return value is 0 modification failed
Boolean b = Stat.execute (SQL); //All
Process results, Release resources
Stat.close ();
Conn.close ();
4) SQL injection:
Through some special string splicing, bypassing the test of the database;
Cases:
SELECT * FROM login where username = ' 1 ' or ' 1 ' = ' 1 ' and password = ' 1 ' or ' 1 ' = ' 1 ';
Improved:
Connection conn = drivermanager.getconnection (URL, user, password);
String sql = "SELECT * from login where username=?" and password=? ";
Gets the precompiled action object in place of the get Operation object
PreparedStatement statement = conn.preparestatement (SQL);
To give? Number assignment question mark starting from 1
String username= "1 ' or ' 1 ' = ' 1";
String password= "1 ' or ' 1 ' = ' 1";
Statement.setstring (1, username);
Statement.setstring (2, password);
ResultSet ResultSet = Statement.executequery ();
Conn.close ();
Statement.close ();
Resultset.close ();
5) Call the stored procedure
CallableStatement stat = conn.preparecall ("Call stored procedure name");
Stat.executeupdate ();
2. Business
1) Concept: Refers to a set of minimum logical Operation unit, which has a plurality of operations composed. Each part of the constituent transaction must be committed at the same time, and if an operation fails, the entire operation is rolled back.
Easy to say is a thing independent units either succeed at the same time or fail at the same time
2)ACID Properties
Atomicity (atomicity): Minimum logical operating Unit
Consistency (consistency): data is in a consistent state during the process
Isolation (Isolation): Multiple concurrent transactions are isolated from each other
Persistence (Durability): Changes to data in a database are permanent
3) use of the transaction
//jdbcutil Self-written easy to connect database tool class import java.sql.connection;import java.sql.drivermanager;import java.sql.resultset;import com.mysql.jdbc.statement;public class jdbcutil {private static string url = null;private static string username = null; private static string password = null;static{//preload driver Try {class.forname (" Com.mysql.jdbc.Driver ");url = " jdbc:mysql://localhost:3306/mydb_01 ";username = " root "; password = "root";} catch (classnotfoundexception e) {e.printstacktrace ();}} Private jdbcutil () {super ();} Public static connection getconnection () throws exception{connection conn = drivermanager.getconnection (Url, username, password); return conn;} Public static voiD close (Connection conn,statement stat,resultset resultset) throws Exception{if ( Conn != null) {conn.close ();} if (stat != null) {stat.close ();} if (resultset != null) {resultset.close ();}} Public static void close (Connection conn,statement stat) throws Exception{ if (conn != null) {conn.close ();} if (stat != null) {stat.close ();}}}
import java.sql.connection;import java.sql.preparedstatement;import java.sql.sqlexception;import Java.sql.savepoint;import org.westos.util.jdbcutil;import com.mysql.jdbc.statement;public class shiwu {public static void main (String[] args) {savepoint point = null; connection conn = null; preparedstatement stat1 = null; preparedstatement stat2 = null; preparedstatement stat3 = null; Preparedstatement stat4 = null;try {conn = jdbcutil.getconnection (); string sql1 = "Update bank set money=money-100 where name= ' qwe '"; string sql2 = "Update bank set money=money+100 where name= ' ASD ';// Turn on transaction conn.setautocommit (false);// enable auto-commit mode for true ; &NBsp;false indicates that auto-commit mode Stat1 = conn.preparestatement (SQL1) is disabled; Stat2 = conn.preparestatement ( SQL2); Stat1.executeupdate (); stat2.executeupdate ();//Set rollback point point = conn.setsavepoint (); string sql3 = "Update bank set money=money-1000 where name= ' qwe '"; string sql4 = "Update bank set money=money+1000 where name= ' ASD '"; Stat3 = conn.preparestatement (SQL3); stat4 = conn.preparestatement (SQL4); Stat3.executeupdate (); System.out.println (1/0); Stat4.executeupdate ();} catch (exception e) {//Once the exception rollback try {//conn.rollback () //rollback to the original state-- Open transaction Conn.rollback (point);//rollback to rollback point} catch (sqlexception e1) {e1.printstacktrace ();}} The finally{//commit transaction submits a series of operations in the transaction to the database Try {conn.commit ();} catch (sqlexception e) {e.printstacktrace ();} Try {jdbcutil.close (conn, (Statement)  STAT1); Jdbcutil.close (null, (statemeNT)  STAT2); Jdbcutil.close (null, (Statement)  STAT3); Jdbcutil.close (null, (Statement) stat4);} catch (exception e) {e.printstacktrace ();}}}
Ii. database connection Pool Products ———— third-party jar packages that connect to the database
1, DBCP
Apache Fund organization products, efficient management of connection objects, need to import DBCP two jar packages
1) How to use hard-coded (not recommended)
To create a connection pool object
Basicdatasource ds = new Basicdatasource ();
Setting parameters
Ds.setdriverclassname ("Com.mysql.jdbc.Driver");
Ds.seturl ("Jdbc:mysql:///mydemo");
Ds.setusername ("root");
Ds.setpassword ("root");
Get Precompiled objects
Connection conn = Ds.getconnection ();
String sql = "SQL statement";
PreparedStatement statement = conn.preparestatement (SQL);
Execute Close Resource
2) How the configuration file is used
Dbcp.properties configuration file
#连接基本设置
Driverclassname=com.mysql.jdbc.driver
Url=jdbc:mysql://localhost:3306/mydemo
Username=root
password=123456
#<!--Extended Configuration Learn more
Use
To create a property collection class object
Properties Properties = new properties ();
Reading data from dbcp.properties text into the properties
Properties.load (New FileInputStream ("Src/dbcp.properties"));
To create a connection pool object
DataSource ds = Basicdatasourcefactory.createdatasource (properties);
Get Precompiled objects
Connection conn = Ds.getconnection ();
String sql = "SQL statement";
PreparedStatement statement = conn.preparestatement (SQL);
Execute Close Resource
2, C3p0 --Need to import C3p0jar package
1) hard-coded format
To create a connection pool object
Combopooleddatasource ds = new Combopooleddatasource ();
Setting parameters
Ds.setjdbcurl ("");
Ds.setdriverclass ("");
Ds.setuser ("");
Ds.setpassword ("");
Get Precompiled objects
Connection conn = Ds.getconnection ();
PreparedStatement statement = conn.preparestatement ("");
Execute Close Resource
2) using the configuration file
Requirements
A: The file name and suffix of the profile are fixed c3p0.properties/c3p0-config.xml
B: The configuration file must be placed in the SRC directory
C3p0.properties configuration file
C3p0.driverclass=com.mysql.jdbc.driver
C3p0.jdbcurl=jdbc:mysql:///mydemo
C3p0.user=root
c3p0.password=123456
C3p0-config.xml configuration file with a named configuration
<named-config name= "PZWJ" >
Use
Combopooleddatasource ds = new Combopooleddatasource ("PZWJ");//can also not write "PZWJ"
Connection conn = Ds.getconnection ();
Subsequent series of operations
3, Dbutils
Apache Fund production, packaging operation object Additions and deletions, need to cooperate with C3P0 use, import c3p0 jar package and dbutils jar package
Of course, C3P0 's configuration files need to be copied in.
1) Use
Creating objects
Combopooleddatasource ds = new Combopooleddatasource ();
Queryrunner runner = new Queryrunner (DS);
Execute SQL statement
String sql = "INSERT into user values (4, ' qwe ')";
Runner.update (SQL); //delete and change operations
2) Query operation
String sql = "SELECT * from user";
list<user> list = runner.query (sql, New Beanlisthandler<> (User.class));
You need to write a Javabean,user class to encapsulate the information you have queried, encapsulate the information, and then save it to the list collection.
By enhancing the for () loop variable user
for (User u:list) {
SYSO (U);
}
Jdbc--java a series of interfaces to the operational database