Step 3: Database Connection

Database connection can be said to be the most basic part of Web Learning and also a very important part. Today we will introduce the database connection to lay the foundation for learning the Real Web below.

There are two methods to connect to a database in Java:

1. ODBC-Open Database Connectivity (Open Database Connectivity): a set of database programming interfaces based on C language. The main function is to provide database access and operations.
All database vendors implement this interface. Different database vendors provide different implementations, that is, third-party support. This programming interface is our standard.

2. JDBC -- Java database connectivity (ODBC for Java)
A set of programming standards specifically provided for the Java language for accessing and operating databases, provided by Sun, are implemented by all database vendors that can be operated by the Java language.

We generally adopt JDBC. Advantages:

1. The efficiency of JDBC is relatively higher.

2. For security reasons, JDBC is higher than ODBC

3. JDBC is very convenient to use. You only need to add the corresponding driver package. Unlike ODBC, JDBC-ODBC bridge driver is also required.

4. Use JDBC to easily replace various types of databases. You only need to change the corresponding driver.

The following describes how to connect to various databases using JDBC:

Common Methods for connecting JDBC to a database are summarized as follows: 1. Connecting JDBC to db2class. forname ("com.ibm.db2.jdbc.net. db2driver "); string url =" JDBC: DB2: // dburl: Port/dbname "cn = drivermanager. getconnection (URL, susr, spwd); 2. JDBC connects to Microsoft sqlserver (Microsoft) class. forname ("com. microsoft. JDBC. sqlserver. sqlserverdriver "); Cn = drivermanager. getconnection ("JDBC: Microsoft: sqlserver: // dbserverip: 1433; databasename = Master", susr, spwd); III. JDBC connection to Sybase (jconn2.jar) class. forname ("com. sybase. jdbc2.jdbc. sybdriver "); Cn = drivermanager. getconnection ("JDBC: Sybase: TDS: dbserverip: 2638", susr, spwd); 4. JDBC connection to 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); 5. JDBC connects to PostgreSQL (pgjdbc2.jar) class. forname ("org. postgreSQL. driver "); Cn = drivermanager. getconnection ("JDBC: PostgreSQL: // dbserverip/mydatabasename", susr, spwd); 6. JDBC connects to Oracle (classes12.jar) class. forname ("oracle. JDBC. driver. oracledriver "); Cn = drivermanager. getconnection ("JDBC: oracle: thin: @ mydbcomputernameorip: 1521: orcl", susr, spwd); 7. Connect to ODBC class through JDBC. forname ("Sun. JDBC. ODBC. jdbcodbcdriver "); connection Cn = drivermanager. getconnection ("JDBC: ODBC:" + sdsn, susr, spwd );

Below I take Microsoft sqlserver 2000 as an example to explain the database connection, we first put the jar package in the lib directory of the project before the connection, Here we only need to jtds-1.2.5.jar

Next, let's take a look at the complete database connection code:

Import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. preparedstatement; import Java. SQL. resultset; public class jdbctest {// connection driver of the database, which generally does not need to be changed to Private Static final string driver = "com. microsoft. JDBC. sqlserver. sqlserverdriver "; // database connection URL. 1433 is the default database port number, and test is the name of the database you want to operate on, Private Static final string url =" JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = test "; // log on to the private sta account TIC final string dbname = "sa"; // logon password Private Static final string dbpass = "123"; Public void test () {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); rsw.psm.exe cutequery (); While (RS. next () {system. out. println (RS. getstring (" Password ") ;}} catch (exception e) {con. close (); RS. close (); PSM. close (); E. printstacktrace ();} finally {If (RS! = NULL) {Rs. Close () ;}if (PSM = NULL) {PSM. Close () ;}if (con! = NULL) {con. Close ();}}}}

 

For the above database connection query, we will analyze it in six steps:
A. Load the JDBC driver:

Before connecting to the database, load the driver of the database to be connected to JVM (Java Virtual Machine), which is achieved through the static method forname (string classname) of Java. Lang. Class.

As shown in the above Code: class. forname ("com. microsoft. JDBC. sqlserver. sqlserverdriver "); we usually need to capture exceptions when connecting separately, that is, add try .. catch

Try{

Class. forname ("com. Microsoft. JDBC. sqlserver. sqlserverdriver ");

}Catch(Classnotfoundexception e ){

System. Out. println ("The driver class cannot be found. Failed to load the driver! ");

E. printstacktrace ();

}

After the driver class is loaded, the driver class instance is registered to the drivermanager class.

 

B. Provide the URL of the JDBC connection and connect:

Code above: (capture exceptions as well)

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 SQL statements, you must obtain the java. SQL. Statement instance. The statement instance can be divided into the following types:

1. Execute static SQL statements. It is usually implemented through a statement instance.

2. Execute dynamic SQL statements. It is usually implemented through the preparedstatement instance.

3. Execute the database stored procedure. It is usually implemented through the callablestatement instance.

Specific implementation methods:

1. Statement stmt = con. createstatement ();

2. The above code: preparedstatement PSM = con. preparestatement (SQL );

3. callablestatement cstmt = con. preparecall ("{call demosp (? ,?)} ");

 

D. Execute SQL statements

The statement interface provides three methods for executing SQL statements: executequery, executeupdate, and execute.

1. resultset executequery (string sqlstring): executes the SQL statement used to query the database and returns a result set object.

2.IntExecuteupdate (string sqlstring): used to execute insert, update, or delete statements and SQL DDL statements, such as CREATE TABLE and drop table.

3. Execute (sqlstring): it is used to execute statements that return multiple result sets, multiple update counts, or a combination of the two.

Specific implementation code:

Resultset rs = stmt.exe cutequery ("select * from ...");

IntRows = stmt.exe cuteupdate ("insert ...");

BooleanFlag = stmt.exe cute (string SQL );

 

E. processing result

Two cases:

1. The number of records affected by this operation is returned when an update is executed.

2. The result returned by executing the query is a resultset object.

• Resultset contains all rows that meet the conditions in the SQL statement, and it provides access to the data in these rows through a set of get methods.

• Use the access method of the result set object to obtain data:

For example:

While (Rs. Next ()){
System. Out. println (Rs. getstring ("apassword "));
}

Another way to process the result set is to obtain the data in the first column of the corresponding table based on the number of the database table column (from left to right, column 1), such as Rs. getstring (1 ).

 

 

F. Disable JDBC objects

After the operation is complete, you need to close all the used JDBC objects to release the JDBC resources. The order of closure is the opposite of the declared order:

1. Disable record set

If (RS! = NULL ){
Rs. Close ();
}

2. Close the Declaration

If (PSM = NULL ){
PSM. Close ();
}

3. Close the connection object

If (con! = NULL ){
Con. Close ();
}

 

The connection to the database is basically over. You can go back to the book with some basic knowledge. Here I also provide a encapsulated database connection operation, which only needs to be called.

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

Next time we will perform database operations