http://blog.9cbs.net/xliujun/archive/2004/07/20/46267.aspx
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 for his driver
(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 on the RESUTSET object's first line, the next time the next method will move the cursor to the first line, making it a current line. Next, the next method will make the cursor from top to bottom each time. 2) Getxxx method Appropriate type 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 float n = = = gtfloat (strcolumnnname2) // strcolumnnname2 for the strsql string System.out.println (S " n);} JDBC uses two methods to identify the column of the Getxxx method to retrieve the value. 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 = gtfloat (2); 3) GetString method Although the GetString method 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 search new SQL3 data type. The SQL3 type will be discussed later. 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 connect. Connection.Close (); is a Complete example: 1) Download and install the Microsoft JDBC after downloading and installing Microsoft JDBC (http://download/sqlsvr2000/INSTALL/2.2.0022/NT5XP/EN-US/Setup.exe)) Introducing the three JAR files msbase.jar, mssqlserver.jar, msutil.jar in the installation directory 3) 3) Create database tempdb, establish table coffees, SQL statements: Create Table Coffeees (COF_NAME VARCHAR (32), SUP_ID INTEGER, Price Float, Sales Integer, Total Integer 4) Add some data to Table Coffee, 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 the 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 strir = "sun.jdbc.odbc.jdbcodbcdriver"; string striver = "com.microsoft.jdbc.sqlser.sqlserverdriver;" // String strconnurl = "JDBC: ODBC: TempdataSource"; string strconnurl = "JDBC: Microsoft: SQLServer: // 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.Print ("" Objset.GetinT (5));} Objconn.close ();} catch (exception e) {system.out.print (E.getMessage ());}}} 5 Using the preparation statement preparedStatement object is a precompiled SQL statement. DBMS does not have to compile the PREPAREDSTATEMENT's SQL statement directly. So instead of Statement objects generally shorten the execution time. Although the PareDStatement object can be used for SQL statements without parameters, but in most occasions is the SQL statement for with parameters.
The usage is as follows: string strupdates = "Update coffees set sales =? Where cool_name like?"; PreparedStatement updates = con.preparestatement (STRUPDATESALES); Updates OtTint (1, 75); // Give the top of the SQL statement Question Numbeo payd value UpdatesaS.setString (2, "colorbian"); // pay the second question mark of the SQL statement to the previous question number (); // Execute Update CoffeeS set sales = 75 where cof_name like 'color' , Can be used as a template to create a SQL statement that constructs multiple strip parameters at a time, simplifies the encoding. 6, the return value of the executeUpdate method EXECUTEQUERY Returns a RESULTSET object, but EXECUTEUPDATE returns an integer value indicating that the number of rows that have been updated in the table is 0 indicated that the following two situations: (1) the statement executed It is an update statement that does not affect any row; (2) is executed a DLL statement. 7. Use a transaction a transaction as a group of statements (one or more statements) performed as a unit, so they are either executed, or all will not be executed. To make two or more statements to make a transaction to disable automatic submission mode, specific method con.setAutoCommit (false); no SQL statement will be submitted, unless you explicitly call CON .Commit () method, implement the transaction. Use transactions to maintain data integrity. Call the Rollback method to cancel a transaction, returning any of the modified data to the previous value. If you get SQLException when you perform one or more statements in a transaction. You should call the ROLLBACK method to cancel the transaction, starting the entire transaction from the head. The ROLLBACK is usually called in the CATCH block to avoid incorrect data. 8. Search WARNING SQLWARNING object is a subclass of SQLException, which is used to process database access warnings. Just like an exception, warning does not terminate the execution of the program; they just remind users - some unpredified things have occurred. Connection objects, Statement objects (including PreparedStatement and CallableStateMent objects) or ResultSet objects are reported. These classes have a getWarnings method, and this method must be called for the first warning reported to the invoke object. If getWarnings returns a warning, you can call the SQLWARNING method getNextWarning to get other warnings. Automatically perform a statement clearing the warning for the previous statement, so warning will not be again. But this also shows that the warning of the extracted statement report must be performed before the next statement is performed. 9. The stored procedure stored procedure is a logical unit consisting of a set of SQL statements for performing specific tasks. The stored procedure is used to encapsulate a set of operations or queries executed on the database server. The stored procedure can be compiled, and then different parameters and results are used, these parameters and results can be any combination of input, output, and input / output parameters.
1) Create a stored procedure using SQL as follows, which is the same method with other DDL statements: String createProcedure = "create procedure SHOW_SUPPLIERS" "as" "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME" "from SUPPLIERS, COFFEES " " where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " " order by SUP_NAME "; Statement stmt = con.createStatement (); stmt.executeUpdate (createProcedure); 2) call a stored procedure from the first step is to create a JDBC CallableStatement object . The CallableStatement object contains a call for the stored procedure; but it does not include the stored procedure itself. Then use the connection CON to create a call to the stored procedure. The part of the lacerans is the syntax of the stored procedure. When the driver encounters "{Call show_suppliers}", it converts this essential syntax into a local SQL used by the database to call the stored procedure behind the CALL. As shown below: Callablestatement cs = con?prepareCall ("{call show_suppliers}); ResultSet RS = cs.executeQuery (); Note that the method for performing CS is ExecuteQuery, because the stored procedure of the CS contains a query, execution A result set is produced. If the stored procedure contains an update or a DLL statement, then use the ExecuteUpdate method. However, sometimes a stored procedure contains multiple SQL statements, so it is not just a result set, not just a new update count or generates a combination of some result sets and update counts. This will have multiple result sets, and you should use the Execute method to perform CallableStatement. The CallableStatement class is a subclass of PreparedStatement, so the CallableStatement object can have an input parameter as the PreparedStateMent object. In addition, the CallableStatement object can also be used with output parameters or input / output parameters.
