Java Getting started the third step of database connection "Go"

Database connection can be said to learn the most basic part of the web, but also a very important part, today we will introduce the database connection for the following to learn the real web lay the foundation

There are two common ways to connect a database in Java:

1.odbc--open database Connectivity (Open Database Connectivity): A set of C-based programming interfaces, the main function is to provide database access and operation
All the database vendors to implement this interface, different database vendors to provide the implementation is not the same, that is commonly referred to as third-party support, and this programming interface is our standard

2.jdbc--java Database Connectivity (ODBC version of Java)
A set of programming standards for accessing operational databases specifically for the Java language, provided by Sun, and implemented by all database vendors that can be manipulated by the Java language

We generally use JDBC, which has the advantage of:

1. JDBC is relatively high in efficiency

2. For security reasons, JDBC is higher than ODBC

3.JDBC is very convenient to use, as long as the corresponding driver package is available, and unlike ODBC also requires JDBC-ODBC bridge driver

4. Using JDBC can easily be exchanged between various databases, as long as you change the corresponding driver.

Here's how to connect a variety of databases using JDBC:

Common JDBC Connection database methods are summarized as follows: First, JDBC Connection db2class.forname ("Com.ibm.db2.jdbc.net.DB2Driver"); String url= "Jdbc:db2://dburl:port/dbname"CN =Drivermanager.getconnection (URL, susr, spwd), JDBC connects to Microsoft SQL Server (Microsoft) Class.forName (" Com.microsoft.jdbc.sqlserver.SQLServerDriver "); cn = Drivermanager.getconnection ("Jdbc:microsoft:sqlserver://dbserverip:1433;databasename=master", SUSR, spwd); Third, JDBC connects Sybase (Jconn2.jar) class.forname ("Com.sybase.jdbc2.jdbc.SybDriver"); cn = Drivermanager.getconnection ("jdbc:sybase:tds:dbserverip:2638", SUSR, spwd); Iv. JDBC Connection MySQL (mm.mysql-3.0.2-Bin.jar) Class.forName ("Org.gjt.mm.mysql.Driver" ); cn = Drivermanager.getconnection ("jdbc:mysql://dbserverip:3306/ MyDatabaseName ", Susr, spwd); Five, JDBC Connection PostgreSQL (Pgjdbc2.jar) class.forname ("Org.postgresql.Driver" ); cn = Drivermanager.getconnection ("jdbc :p ostgresql://dbserverip/mydatabasename ", Susr, spwd); Six, JDBC Connection Oracle (Classes12.jar) class.forname ("Oracle.jdbc.driver.OracleDriver" ); cn = Drivermanager.getconnection ("Jdbc:oracle:thin: @MyDbComputerNameOrIP: 1521:orcl", Susr, spwd); Vii. JDBC Connectivity ODBC Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver" ); Connection cn = Drivermanager.getconnection ("JDBC:ODBC:" + sDsn, SUSR, spwd);       

Here I use Microsoft SQL Server 2000 as an example of database connectivity, we put the jar package in the project's Lib directory before connecting, here we only need to Jtds-1.2.5.jar

Let's take a look at the full Connection database code first:

ImportJava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.PreparedStatement;ImportJava.sql.ResultSet;PublicClassjdbctest {//Database connection driver, generally do not need to changePrivateStaticFinal String driver= "Com.microsoft.jdbc.sqlserver.SQLServerDriver";//The url,1433 of the database connection is the default database port number, and test is the name of the database you are currently working onPrivateStaticFinal String url= "Jdbc:microsoft:sqlserver://localhost:1433;databasename=test";//Login accountPrivateStaticFinal String dbname= "sa";//Password for loginPrivateStaticFinal String dbpass= "123";PublicvoidTest () {Connection con=Null; PreparedStatement psm=Null; ResultSet rs=Null;Try{Class.forName (DRIVER); con =Drivermanager.getconnection (Url,dbname,dbpass); String sql= "select* from admin where id=1"; psm=con.preparestatement (SQL) Rs=psm.executequery (); Span style= "color: #0000ff;" >while (Rs.next ()) {System.out.println (rs.getstring ("Apassword" )); }}catch (Exception e) {con.close (); Rs.close (); Psm.close (); E.printstacktrace (); }finally{if (rs!=< Span style= "color: #0000ff;" >nullif (Psm==nullif (Con!=null 

For the above database connection query, we have 6 steps to analyze:
A. Load the JDBC driver:

Before connecting to a database, you first load the driver of the database you want to connect to the JVM (Java Virtual machine), which is implemented by the static method forname (String className) of the Java.lang.Class class.

As the above code: Class.forName ("Com.microsoft.jdbc.sqlserver.SQLServerDriver"); When we make a separate connection, we typically make an exception capture, which is the addition of a try. Catch

Try {

Class.forName ("Com.microsoft.jdbc.sqlserver.SQLServerDriver");

}catch(ClassNotFoundException e) {

System.out.println ("Driver class not found, load driver failed!")   ");

E.printstacktrace ();

}

After a successful load, an instance of the driver class is registered in the DriverManager class.

B. Provide a URL for the JDBC connection and connect it:

As in the above code: (also for exception capture)

private static final String url= "Jdbc:microsoft:sqlserver://localhost:1433;databasename=test";
private static final String dbname= "sa";
private static final String dbpass= "123";

try{

con = drivermanager.getconnection (url,dbname,dbpass);

}catch{
SYSTEM.OUT.PRINTLN ("Database connection failed");

E.printstacktrace ();

}

C. Create a statement

• To execute an SQL statement, you must obtain an java.sql.Statement instance, and the statement instance is divided into the following 3 types:

1. Execute the static SQL statement. Typically implemented through statement instances.

2. Execute the dynamic SQL statement. Typically implemented through PreparedStatement instances.

3. Execute the database stored procedure. Typically implemented through CallableStatement instances.

The specific implementation method:

1.Statement stmt = Con.createstatement ();

2. As above code: PreparedStatement PSM = con.preparestatement (sql);

3.CallableStatement cstmt = Con.preparecall ("{Call Demosp (?,?)}");

D. Executing SQL statements

The statement interface provides three ways to execute SQL statements: ExecuteQuery, executeupdate, and execute

1.ResultSet executeQuery (String sqlString): Executes the SQL statement that queries the database, returning a result set (ResultSet) object.

2.int executeupdate (String sqlString): Used to execute INSERT, UPDATE, or DELETE statements and SQL DDL statements such as CREATE table and drop table, etc.

3.execute (sqlString): Used to perform statements that return multiple result sets, multiple update counts, or a combination of both.

Specific implementation code:

ResultSet rs = stmt.executequery ("SELECT * from ...");

int rows = stmt.executeupdate ("INSERT into ...");

boolean flag = Stmt.execute (String sql);

E. Processing results

Two cases:

1. The update is performed to return the number of records affected by this operation.

　　2. The result returned by the execution query is a ResultSet object.

resultset contains all rows that conform to the conditions in the SQL statement, and it provides access to the data in those rows through a set of Get methods.

• Get data using the access method of the result set (ResultSet) object:

As in the above example:

while (Rs.next ()) {
System.out.println (rs.getstring ("Apassword"));
}

There is another way to work with result sets: According to the number of the database table column (from left to right, the first column 1), such as rs.getstring (1), to get the data from the first row in the corresponding table

F. Closing a JDBC object

After the operation is complete, all the JDBC objects used are closed to release the JDBC resource, and the order of closing and declaration is reversed:

1. Closing a recordset

if (rs!=null) {
Rs.close ();
}

2. Closing the statement

if (psm==null) {
Psm.close ();
}

3. Close the Connection object

if (con!=null) {
Con.close ();
}

Connect the database basically end, contains some basic knowledge of the people go back to see the book, I also provide a packaged database connection operation, only need to call to

Http://www.cnblogs.com/shenliang123/archive/2012/04/19/2456665.html

Next time we will do the actual operation of the database

Note:

PreparedStatement and statement differences in Java

First:      
     When executing SQL statements, the database has a little advantage when using the PreparedStatement statement: Because the database is preparedstatement  The
statement is precompiled and the next time the same SQL statement is executed, The database side is no longer pre-compiled, and directly with the database buffer, improve the efficiency of data access (but try to use as much as possible?). If the SQL statement executes only once and is no longer reused.  
Second:  
    in a JDBC application, if you are already a slightly level developer, You should always replace statement with PreparedStatement. In other words, do not use statement.  at any time for the following reasons:  
I. Readability and maintainability of the code.
Although using PreparedStatement instead of statement will make the code a few more lines, this code is both readable and maintainable. Much higher than the code used directly with statement:   
Stmt.executeupdate ("insert into tb_name  (COL1,COL2,COL2,COL4)  values  ('" +var1+ "', '" + var2+ "'," +var3+ ", '" +var4+ "),   
Perstmt = con.preparestatement (" Insert into  tb_name  (COL1,COL2,COL2,COL4)  values  (?,?,?,?) ");  perstmt.setstring (1,VAR1);  perstmt.setstring (2,VAR2);  perstmt.setstring (3,VAR3);  Perstmt.setstring (4,VAR4);  perstmt.executeupdate ();  

Needless to say, for the first method. Don't tell anyone else to read your code, it's your own time to read it, you will feel sad.   
Two. PreparedStatement maximize performance.  
Each database will do its best to provide maximum performance optimizations for precompiled statements. Because precompiled statements are likely to be called repeatedly. So the statements are cached after the compiler compiles the db. At the next call, as long as the same precompiled statement does not need to compile, as long as the parameters are passed directly into the compiled statement execution code (equivalent to a number of a culvert) 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:  insert into tb_name  (col1,col2)  values  (' One ', '; insert into  ') tb_name  (col1,col2)  values  (' One ', ' total ')  
Even if the same operation but because the data content is not the same, so the entire statement itself does not match, There is no meaning to 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 precompiled statements are bound to be cached, the database itself will be a policy, For example, the use of frequency and other factors to determine when to no longer cache the pre-compiled results. To save more space to store new precompiled statements.   
Three. The most important point is a significant increase in security. (SQL injection)  

string sql =  ' select * from tb_name where name=  ' "+varname+" '  and passwd= ' "+varpasswd+" ' ";  
If we pass ['  or  ' 1 '  =  ' 1] as varpasswd. User name Free And see what it's going to be?   
select * from tb_name =  ' Casual '  and passwd =   '  or  ' 1 '  =  ' 1 ';  because ' 1 ' = ' 1 ' is sure to be established, so can be verified by any pass. What's more:  the [';d rop table  Tb_name;] Incoming in as varpasswd:  
select * from tb_name =  ' Casual '  and passwd =   ';d rop table tb_name; Some databases are not going to make you successful, but there are many 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,;