First, the abstract interface of JDBC
Figure 3. Main interfaces and interrelations in the SQL package
These interfaces are stored in the Java's SQL package, their names and basic functions are:
l java.sql.driverManager
Manage the drive to support the creation of the drive and database connection
l java.sql.connection
The Connection object represents the connection to the database. The connection process includes the resulting SQL statement and the result returned on the connection. An application can have one or more connections with a single database, or can be connected to many databases.
l java.sql.statement
The Statement object is used to send SQL statements to the database.
There is actually three statement objects, which are all packed containers that perform SQL statements on a given connection: Statement, PreparedStatement (it comes from Statement) and CallableStatement (it is inherited from PreparedStatement). They are all dedicated to sending specific types of SQL statements: Statement is created by method CreateStatement. Simple SQL statement used to perform without parameters;
PreparedStatement is created by the method PrepareStatement. Precompiled SQL statement for performing strips or without IN parameters;
The CallableStatement object is used to perform calls for database stored procedures.
This type of common method:
Method EXECUTEQUERY is used to generate a single result set statement, such as a SELECT statement.
Methods EXECUTEUPDATE are used to perform INSERT, UPDATE, or DELETE statements, and SQL DDL (data definition language) statements, such as Create Table, and Drop Table. The effect of INSERT, UPDATE or DELETE statement is to modify a column or multiple columns in a table in a table. The return value of ExecuteUpdate is an integer that indicates the number of rows affected (ie update counts). For CREATE TABLE or DROP TABLE, the return value of ExecuteUpdate is always zero.
Methods EXECUTE is used to perform statements that return multiple result sets, multiple update counts, or two combinations. Because most programmers do not need this advanced feature, this overview will be described in separate section.
l java.sql.resultset
Data results generated after the execution of the SQL declaration
Second, connect various database mode quick check table
Below, various databases use the JDBC connection, can be used as a manual.
1, Oracle8 / 8i / 9i database (Thin mode)
Class.Forname ("Oracle.jdbc.driver.OracleDriver"). NewInstance ();
String Url = "JDBC: Oracle: Thin: @localhost: 1521: ORCL"; // ORCL is the SID of the database
String User = "test";
String password = "test";
Connection conn = drivermanager.getConnection (URL, User, Password);
2, DB2 database
Class.Forname ("com.ibm.db2.jdbc.app.db2driver") .newinstance ();
String url = "jdbc: db2: // localhost: 5000 / sample"; // Sample for your database name string user = "admin";
String password = ""
Connection conn = drivermanager.getConnection (URL, User, Password);
3, SQL Server7.0 / 2000 database
Class.Forname ("com.microsoft.jdbc.sqlserver.sqlserverdriver). NewInstance ();
String Url = "JDBC: Microsoft: SQLServer: // localhost: 1433; DatabaseName = MYDB";
// mydb is a database
String User = "sa";
String password = ""
Connection conn = drivermanager.getConnection (URL, User, Password);
4, Sybase database
Class.Forname ("com.sybase.jdbc.sybdriver). Newinstance ();
String Url = "JDBC: Sybase: TDS: Localhost: 5007 / MyDB"; // mydb is your database name
Properties sysprops = system.getproperties ();
Sysprops.put ("User", "UserID");
Sysprops.put ("Password", "User_Password");
Connection conn = drivermanager.getConnection (URL, SYSPROPS);
5, Informix database
Class.Forname ("com.informix.jdbc.ifxdriver). NewInstance ();
String URL =
"JDBC: Informix-SQLI: //123.45.67.89: 1533 / mydb: informixserver = myserver;
User = testuser; password = TestPassword "; // mydb is the database name
Connection conn = drivermanager.getConnection (URL);
6, MySQL database
Class.Forname ("Org.gjt.mm.mysql.driver"). NewInstance ();
String url = "jdbc: mysql: // localhost / mydb? User = Soft & Password = SOFT1234 & UseRode = True & Characterencoding = 8859_1"
// mydb is the database name
Connection conn = drivermanager.getConnection (URL);
7, PostgreSQL database
Class.Forname ("Org.PostgreSql.driver). NewInstance ();
String url = "jdbc: postgreSQL: // localhost / mydb" // mydb is the database name
String User = "myuser";
String password = "mypassword"; connection conn = drivermanager.getConnection (URL, User, Password);
8, Access database straight with ODBC
Class.Forname ("Sun.jdbc.odbc.jdbcodbcdriver");
String URL = "JDBC: ODBC: driver = {Microsoft Access Driver (* .mdb)}; dbq =" Application.getRealPath ("/ data / reportdemo.mdb");
Connection conn = drivermanager.getConnection (URL, "", "");
Statement stmtnew = conn.createstatement ();
Third, use in Oracle
1 registration
Driver driver = null;
String Url = NULL;
String user = NULL;
String password = NULL;
Driver = (driver) Class.Forname ("Oracle.jdbc.driver.OracleDriver"). NewInstance ();
Drivermanager.RegisterDriver (driver);
URL = "JDBC: Oracle: Thin: @ 10.40.90.103: 1521: ZXIN";
User = "system";
Password = "manager";
CONN = DriverManager.getConnection (URL, User, Password);
2 Execute SQL statements in Java
V SQLSTR = "begin update zxinoda.users set name =? where id = ?; commit; end;
STMT = conn.preparestatement (SQLSTR);
Stmt.setstring (1, "gaox1");
Stmt.setint (2, 1);
Stmt.executeUpdate ();
v sqlstr = "SELECT ID, NAME, Password from zxinoda.users where id =?";
STMT = conn.preparestatement (SQLSTR);
Stmt.setint (1, 1);
RS = stmt.executeQuery (); // Simple SQL statement execution can get results set
While (rs.next ()) {
System.err.Println ("ID =" rs.getint (1) ", Name =" gtstring (2));
}
3 Java transfer storage procedure
v sqlstr = "{call zxinoda.sp_addsoftphone (?,)}";
STMT = conn.preparecall (SQLSTR);
Stmt.setstring (1, "6130444");
Stmt.executeUpdate ();
V Sqlstr = "{CALL ZXINODA.SP_ADDSMRCV (?)}"; stmt = conn.prepareCall (SQLSTR);
Stmt.setint (1, 1);
Stmt.registerOutparameter (2, ORACletYpes.integer);
Stmt.executeQuery ();
System.err.Println ("Return =" stmt.getint (1)); // Note: If you use a stored procedure, you cannot get the result set, you can't use RS.Getint (1),
v sqlstr = "{call zxinoda.sp_getuserinfo5 (?,?)}"
STMT = conn.preparecall (SQLSTR);
Stmt.setint (1, 1);
Stmt.registerOutparameter (2, ORACLETYPES.CURSOR);
Stmt.executeQuery ();
RS = (ORACLALLABLESTATEMENT) .GETCURSOR (2); // Note: If you use a stored procedure, you cannot get the result set. If you need to get the result set, you need to use a cursor.
While (rs.next ()) {
System.err.Println ("ID =" rs.getint (1) ", login =" gtstring (2));
