1st: JDBC foundation (http://gceclub.sun.com.cn/staticcontent/html/jdbc/ï‰)
1, establish a database connection
First create a connection to the DBMS you want to use. This includes two steps: (1) load the driver; (2) Create a connection.
(1) Load the driver class.Forname ("Sun.jdbc.odbc.jdbCodbcDriver"); // String is the class name of the programs for him
(2) Creating a connection
The second step of creating a connection is to connect to DBMS using the appropriate driver. The following code line demonstrates general usage:
// URL string is the sub-protocol used, that is, in the JDBC URL in JDBC: Subsets Connection Con = DriverManager.getConnection (URL, "MyLogin", "MyPassword");
2, create and execute the JDBC statement
The STATEMENT object can send the SQL statement to the DBMS. This will only create a statement object that passes the SQL statement you want to perform to the appropriate method and then execute the Statement object. Statement Stmt = con.createStatement (); for the SELECT statement, the method used is ExecuteQuery. The method used by the statement created or modified, is ExecuteUpdate. // strsql string is a DDL (Data Description Language) statement stmt.executeUpdate (strsql); 3, the processing result jdbc returns the result set to the ResultSet object // strsql string is a query statement ResultSet RS = Stmt.executeQuery (strsql );
1) NEXT method
Variable RS (an instance of ResultSet) contains the table shown in the result set. To access the name and unit price to each row, the value is retrieved according to their type.
The next method moves the cursor to the next row, making the line (called the current line) a row that can be operated there. Since the cursor is initially positioned above the RESUTSET object,
So the first time called the next method to move the cursor to the first line, making it the current line. Next, the next method will make the cursor from top to bottom each time.
2) getxxx method
The appropriate type of Getxxx method can be used to search the values in columns. The method of retrieving the VARCHAR SQL type value is getString. The method of retrieving this type of value is getfloat.
Each time the next method is called, the next line is a current line, and the loop has continued until the RS has no behavior to move forward.
While (rs.next ())
{
String s = rs.getstring (strcolumnname1); // strcolumnname1 is the column name in the strsql string
Float n = rs.getfloat (strcolumnname2) // strcolumnname2 is the column name in the strsql string
System.out.println (S "" N);
}
JDBC uses two ways to identify the column of the Getxxx method. One is to specify the column name, which is also the previous example.
The other is to specify the column index (sequence number), 1 represents the first column, 2 represents the second column, and so on.
String s = rs.getstring (1); float n = rs.getfloat (2);
3) GetString method
Although it is recommended to retrieve the data of the CHAR and VARCHAR SQL types, it is also possible to retrieve other basic SQL type data (but it is not available to retrieve the new SQL3 data type. This tutorial will discuss the SQL3 type). There are many advantages to retrieve all values with getString, but also have limitations.
If you retrieve the data of the Numeric type, getString will convert the numeric value to the String object of Java, so that the data must be converted back to the NUMERIC type before the data is required.
This is unpredictable in the case where the value is always as a string. If you want the program to retrieve any standard SQL types other than the SQL3 type, you can use the getString method.
4, turn off the connection
Connection.Close ();
Here is a complete example:
1) Download and install Microsoft JDBC (http://download/sqlsvr2000/install/2.2.0022/NT5XP/EN-US/Setup.exe)
2) After installing Microsoft JDBC, the three JAR files msbase.jar in the installation directory Msbase.jar, MSSQLSERVER. Jar, Msutil.jar
3) Establish a database TEMPDB, establish a table CoffeeES, SQL statement is:
Create Table Coffeees (COF_NAME VARCHAR (32), SUP_ID INTEGER, Price Float, Sales Integer, Total Integer
4) Add some data to Table Coffeees, such as:
INSERT INTO Coffeees Values ('Colombian', 101, 7.99, 0, 0);
5) Enter the following source file and execute
Import java.sql. *;
/ ** * @Author liujun Todo to change TEMPLATE for this generated type comment go * to window - preferences - java - code style - code templates * / public class jdbc_01 {
Public static void main (string [] args) {showTable ();
Public static void showtable () {
// String strDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; String strDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; // String strConnUrl = "jdbc: odbc: TempDataSources"; String strConnUrl = "jdbc: microsoft : SQL Server: // localhost: 1433; DatabaseName = Tempdb "; string strsql =" select * from coffeees ";
Try {// Load Driver Class.Forname (Striver); // Create Connection Objconn = DriverManager.getConnection (StrConnurl, "SA", "); // Create a Statement Object Statement ObjState = Objconn.createment (); / / Jdbc returns the result set to the resultset object = objStatement.executeQuery (strsql); // Get column number of query results int LCloumNCount = objset.getMetadata (). GetColumnCount (); system.out.println ("query results "); // Display column name for (int i = 1; i <= lcloumncount; i ) {system.out.print (objset.getMetadata (). GetColumnname (i) " ");} system.out .println (); // Display result while (objSet.Next ()) {// Display COF_NAME System.out.Print (" Objset.getstring (1))); // Displays Sup_ID Price Sales Total System.out. Print ("" Objset.ge Tint (2)); // Display price system.ord.print ("" Objset.getfloat (3)); // Display Sales System.Print (" ObjSet.Getint (4)); // Show Total System.out.print ("" ObjSet.Getint (5));
Objconn.close ();
} Catch (exception e) {system.out.print (E.getMessage ());}}
}
