MySQL---Database from getting started to Big God series (vii)-java access to database configuration and simple usage execute

Read the connection string from the action profile properties, the data connection through the string, you need to write three files where two are Java classes, one is a file with the suffix. Properties, and the file is placed under the SRC working directory.

The file with the suffix. Properties is named here as Jdbc.properties, where the code is as follows:

##MySQLdriver=com.mysql.jdbc.Driverurl=jdbc:mysql:///hncu?useUnicode=true&characterEncoding=utf-8username=rootpassword=1234##Oracle#driver=oracle.jdbc.driver.OracleDriver#url=jdbc:oracle:thin:@localhost:1521:orcl#username=scott#password=tiger

Can configure multiple database code, form a framework, where only the MySQL and Oracle database configuration, if the database is changed, the corresponding database of the # Number of comments removed, the original code to use the # comment can be.

Next, use a single example to write a connection factory class Connfactory.java:

 PackageCn.hncu.util;ImportJava.io.IOException;ImportJava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.SQLException;ImportJava.util.Properties; Public  class connfactory {    Private StaticConnection con =NULL;//Static block    Static{Try{//Read configuration fileProperties p =NewProperties (); P.load (ConnFactory.class.getClassLoader (). getResourceAsStream ("Jdbc.properties")); String Drive = P.getproperty ("Driver"); String URL = p.getproperty ("url"); String user = P.getproperty ("username"); String Password = p.getproperty ("Password");            Class.forName (drive);        con = drivermanager.getconnection (url, user, password); }Catch(IOException e) {Throw NewRuntimeException ("configuration file Exception", e); }Catch(ClassNotFoundException e) {Throw NewRuntimeException ("Drive.class file Unexpected", e); }Catch(SQLException e) {Throw NewRuntimeException ("Database access exception occurred", e); }    } Public StaticConnectiongetconnection(){returnCon } Public Static void Main(string[] args)    {System.out.println (getconnection ()); }}

Demonstrates the 4 execution methods in statement:

1, ExecuteQuery (),
2, execute (),
3, Executeupdate (),
4, ExecuteBatch ().

First create a table book:

 Create table Book (id int auto_increment primary key, name varchar (+), price numeric(5,2), birth datetime); Insert  into book values(1,' Oracle ',88.88, ' 2015-07-12 20:42:12 '); Insert  into book values(2,' MySQL ',38.66,' 2015-07-12 19:22:12 ');

ExecuteQuery ()

@Test public void results () throws sqlexception{StatementSt= Connfactory. getconnection(). Createstatement();String sql ="SELECT * from book";ResultSet res =St. ExecuteQuery(SQL);while (res. Next()) {Integer id = Res. GetInt(1);String name = Res. getString(2);Double Price = Res. GetDouble("Price");Attention! This is the way to get date-time data by String birth = Res. GetDate(4)+" "+res. GetTime(4);System. out. println(id+","+name+","+price+","+birth);} connfactory. getconnection(). Close();}

Only query statements can be executed

Execute ()

@Test public void Execute () throws sqlexception{StatementSt= Connfactory. getconnection(). Createstatement();Test statement://Test add data//string sql ="INSERT into book (Name,price,birth) VALUES (' XML ', 23.30, ' 2014-09-08 12:00:05 ')";Test delete data//string sql ="Delete from book where id= ' 2 '";Test query data String sql ="SELECT * from book";True if the first result is a result set.        otherwise, false. Boolean boo =St. Execute(SQL);System. out. println(Boo);if (boo) {ResultSet rs =St. Getresultset();while (RS. Next()) {//here only outputs2A little bit of a hint. System. out. println(RS. GetInt(1)+","+rs. getString(2));}} connfactory. getconnection(). Close();}

The statement of adding, deleting, changing and checking can be executed. Just the result of the query is to tell the success or not, if you want to get the results of the query, you have to use "st.getresultset ()" To get

Executeupdate ()

@Test     Public void executeupdate()throwssqlexception{Statement st = Connfactory.getconnection (). createstatement ();//IncreaseString sql ="INSERT into book (Name,price,birth) VALUES (' Red mansions ', 85.66, ' 2013-10-08 12:00:05 ')";//Modify        //string sql= "update book set price=price*1.1";        //Delete        //string sql = "Delete from book where id=1";        //Query--cannot be queried!!!         //string sql = "SELECT * from book";        intnum = st.executeupdate (SQL);//Return value is the number of rows affectedSYSTEM.OUT.PRINTLN (num); }

can only increment, delete, change, cannot execute query statement

ExecuteBatch ()

@Test      Public void Batchdemo()throwssqlexception{Connection con = connfactory.getconnection (); String sql ="INSERT into book (Name,price, birth) VALUES (' AAA ', 11.11, ' 2013-11-28 19:00:15 ')"; Statement st = Con.createstatement (); for(intI=0;i<5; i++) {if(i==2){//Wrong MySQL code! sql ="INSERT into book (Name,price,birth) VALUES (' AAA ', ' AA ', ' 2013-11-28 19:00:15 ')";        } st.addbatch (SQL); } sql ="Update book Set price = price* where price<30"; St.addbatch (SQL);intA[] = St.executebatch (); for(intX:A) {System.out.println (x);    } connfactory.getconnection (). Close (); }

Execute batch-itself without a transaction, if one of the SQL statements is hung, subsequent SQL execution fails, and the preceding is valid. If you want a transaction, also use: Con.setautocommit (false) +try-cacth+ Rollback/commit

Preparestatement:

The PreparedStatement interface in Java,servlet Inherits statement and differs in two ways: some argue that, in a JDBC application, if you're already a bit of a level developer, You should always replace statement with PreparedStatement. In other words, do not use statement at any time.

The PreparedStatement instance contains the compiled SQL statement. This is to make the statement "ready". The SQL statement contained in the PreparedStatement object can have one or more in parameters. The value of the in parameter was not specified when the SQL statement was created. Instead, the statement retains a question mark for each in parameter ("? ") as a placeholder. The value of each question mark must be provided by the appropriate Setxxx method before the statement is executed.

Advantages:
I. Readability and maintainability of the code .
Although using PreparedStatement instead of statement will make the code a few more lines, this code can be used in terms of readability and maintainability. It is much higher than the code that directly uses statement:
Example:

stmt. Executeupdate("Insertintotb_name (COL1,COL2,COL2,COL4) VALUES ('"+var1+"', '"+var2+"',"+var3+", '"+var4+"')");Perstmt=con. Preparestatement("Insertintotb_name (COL1,COL2,COL2,COL4) VALUES (?,?,?,?)");Perstmt. setString(1, var1);Perstmt. setString(2, VAR2);Perstmt. setString(3, VAR3);Perstmt. setString(4, VAR4);Perstmt. Executeupdate();

Two. PreparedStatement the maximum possible performance improvement.

Each database will do its best to provide maximum performance optimizations for precompiled statements. Because the precompiled statement is likely to be called repeatedly. So the execution code that is compiled by the compiler of the DB is cached, so the next call will not need to be compiled as long as it is the same precompiled statement. As long as the parameters are passed directly into the compiled statement execution code (equivalent to a culvert number) it will be executed. This is not to say that only one connection of the precompiled statements executed more than once is cached, but for the entire DB, As long as the precompiled statement syntax matches the cache. At any time, it can be executed without having to compile again. In statement statements, even though the same operation, the chances of matching the entire statement are minimal because of the different data for each operation, which is almost unlikely to match. For example:

　　insertintotb_name(col1,col2)values(‘11‘,‘22‘);　　insertintotb_name(col1,col2)values(‘11‘,‘23‘);

Even though the same operation is not the same as the data content, the entire statement itself does not match, there is no meaning of the cached statement. The fact is that there is no database that executes code caches after the normal statement is compiled.
Of course not. So the precompiled statements are bound to be cached, and the database itself uses a strategy, such as frequency, to determine when the pre-compiled results are no longer cached. To save more space to store new precompiled statements.

　three. The most important point is a significant increase in security.
Even so far, some people don't even know the basic semantics of SQL syntax.

　String sql="select * from tb_name where name=‘"+varname+"‘ and passwd=‘"+varpasswd+"‘";

If we pass [' or ' 1 ' = ' 1] in as varpasswd. User name feel free to see what will become?

select * from tb_name =‘ 随意 ‘ and passwd=‘‘ or ‘1‘=‘1‘;

Because ' 1 ' = ' 1 ' is sure to be true, so you can pass any validation. What's more:

To [‘;drop table tb_name;] Pass in as a varpasswd:

select * from tb_name=‘ 随意 ‘ and passwd=‘‘ ;drop table tb_name;

Some databases will not make you successful, but there are a number of databases that can make these statements executable.

And if you use precompiled statements. Anything you pass in will not have any matching relationship with the original statement. As long as you use precompiled statements all the time, you don't have to worry about the incoming data. If you use ordinary statement, you may want to make a decision on the drop,;

Impersonate a user login:

It is easy to be black if you use ordinary statement.

Stud table:

Impersonate the user input ID and name login:

@Test//Will not be black: if the input name value is: A ' or ' 1 ' = ' 1     Public void Login() throws sqlexception{Connection con = connfactory.getconnection (); Scanner sc =NewScanner (System.inch);        String id = sc.next ();        String name = Sc.next (); System. out. println (id+","+name);//Create a preprocessing statement objectString sql ="SELECT COUNT (*) from stud where id=? and sname =? ";//Where the user enters, use "?" (called a placeholder) to fill inPreparedStatement PST = con.preparestatement (SQL);//Set the value---set parameters for the placeholderPst.setstring (1, id);//Set a value for the first parameterPst.setstring (2, name);//Set a value for the second parameterResultSet rs = Pst.executequery ();//cannot pass parameter SQL hereRs.next ();//Because there must be only one number of queries here, just take one parameter.        intn = rs.getint (1);if(n<=0) {System. out. println ("Login failed ..."); }Else{System. out. println ("Login Successful ...");    } con.close (); }

This way, no matter what symbols the user enters, you must enter the correct ID and name.

PreparedStatement performing batch Processing

@Test     Public void Prebatchdemo()throwssqlexception{Connection con = connfactory.getconnection (); String sql ="INSERT into book (Name,price,birth) VALUES (?,?, ' 2013-11-28 19:00:15 ')"; PreparedStatement PST = con.preparestatement (SQL); for(intI=0;i<5; i++) {pst.setstring (1,"BB"+i); Pst.setdouble (2, -+i);        Pst.addbatch (); } sql="Update book Set price = price*1.1 where price<30"; Pst.addbatch (SQL);intA[] = Pst.executebatch (); for(intX:A) {System.out.println (x);    } connfactory.getconnection (). Close (); }

MySQL---Database from getting started to Big God series (vii)-java access to database configuration and simple usage execute