Jdbc--java a series of interfaces to the operational database

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