JDBC test programs for MySQL and Oracle

ArticleDirectory

• 2.1 connect to a MySQL database instance
• 2.2mysqljdbc instance parsing (PS: 2011-11-17 supplement)
• 3.1 connect to the oralce database instance
• 3.2myoraclejdbc instance Parsing

0. References

Reference 1: http://dustin.iteye.com/blog/44291

1. Preface

When you test whether the database is correctly connected, J2EE development first needs to do so, because if the database connection is incorrect, subsequent errors cannot be correctly determined. You must first exclude database connection errors. The following describes the connection test between MySQL and Oracle databases in detail.Program

2. MySQL database connection instance

First, we provide the MySQL database connectionCodeAnd then parse the code in detail. The code example is as follows:

2.1 connect to a MySQL database instance View code

 Import  Java. SQL. connection;  Import  Java. SQL. drivermanager; Import  Java. SQL. resultset;  Import  Java. SQL. statement;  Public   Class  Mysqljdbc {  Public   Static   Void  Main (string ARGs []) {  Try  {  /*  * Method 1: load the MySQL JDBC driver */              //  Class. forname ("org. gjt. Mm. MySQL. Driver ");              /*  * Method 2: load the MySQL JDBC driver  */  Class. forname ( "Com. MySQL. JDBC. Driver" );  /*  * Method 3: Create a com. MySQL. JDBC. Driver instance.  */              //  Com. MySQL. JDBC. Driver driver = new COM. MySQL. JDBC. Driver ();             /*  * Method 4: Create a com. MySQL. JDBC. Driver instance.  */              //  New COM. MySQL. JDBC. Driver ();  System. Out. println ( "Success loading MySQL driver! " );}  Catch  (Exception e) {system. Out. Print ( "Error loading MySQL driver! " ); E. printstacktrace ();}  Try  {Connection connect =Drivermanager. getconnection ( "JDBC: mysql: // localhost: 3306/test", "root", "123456" );  //  The connection URL is JDBC: MySQL  //  Server address/Database Name  //  The following two parameters are respectively the login user name and password System. Out. println ("Success connect MySQL server! " ); Statement stmt = Connect. createstatement (); resultset rs = Stmt.exe cutequery ("select * From userinfo" ); While  (Rs. Next () {system. Out. println (Rs. getstring ( "Username" ));}}  Catch  (Exception e) {system. Out. Print ( "Get data error! " ); E. printstacktrace ();}}} 

The running result is as follows:

 
Success loading MySQL driver! Success connect MySQL server! Zhangsanlisi
 

2.2mysqljdbc instance parsing (PS: 2011-11-17 supplement)

As you may see, we only need to ensure the correspondingThe driver class has been loaded into JVM.AndClass initialization is completed.That's all, but there is no particular idea about how to implement this function. AboveFour MethodsYou can achieve this function, so the program can run normally.We usually see that method 2 is used to load the driver class to the JVM. This is also the method used in most teaching materials.

However, after my tests, even if the com. MySQL. JDBC. driver is not loaded into the JVM, the program can run normally. The program is as follows: View code

 Import  Java. SQL. connection;  Import  Java. SQL. drivermanager;  Import Java. SQL. resultset;  Import  Java. SQL. statement;  Public   Class  Mysqljdbc {  Public   Static   Void  Main (string ARGs []) {  Try  {Connection connect = Drivermanager. getconnection ( "JDBC: mysql: // localhost: 3306/test", "root", "123456");  //  The connection URL is JDBC: MySQL  //  Server address/Database Name  //  The following two parameters are respectively the login user name and password System. Out. println ("Success connect MySQL server! " ); Statement stmt = Connect. createstatement (); resultset rs = Stmt.exe cutequery ("select * From userinfo" );  While  (Rs. Next () {system. Out. println (Rs. getstring ( "Username" ));}}  Catch  (Exception e) {system. Out. Print ( "Get data error! " ); E. printstacktrace ();}}} 

The running result is:

 
Success connect MySQL server! Zhangsanlisi
 

If you remove the mysql-connector-java-5.1.18-bin.jar loaded in the program, run the program again and report an error as follows:

 
Get data error! Java. SQL. sqlexception: no suitable driver found for JDBC: mysql: // localhost: 3306/test
 

It is normal to report the error, and Oracle will also package similar errors later. Here we will first list the Errors for later comparison.

3. connect to an Oracle database instance

Similarly, we first provide the code instance for Oracle database connection.

3.1 connect to the oralce database instance View code

Import  Java. SQL. connection;  Import  Java. SQL. drivermanager;  Import  Java. SQL. resultset;  Import  Java. SQL. statement;  Import  Oracle. JDBC. Driver. oracledriver;  Public   Class  Myoraclejdbc {  Public   Static  Void  Main (string ARGs []) {  Try  {  /*  * Method 1: load the oracle JDBC driver  */  Class. forname ( "Oracle. JDBC. Driver. oracledriver" );  /*  * Method 2: Create an oracle. JDBC. Driver. oracledriver instance.  */              // Oracle. JDBC. Driver. oracledriver driver = new Oracle. JDBC. Driver. oracledriver ();                          /*  * Method 3: Create an oracle. JDBC. Driver. oracledriver instance.  */              //  New Oracle. JDBC. Driver. oracledriver ();  System. Out. println ( "Success loading Oracle driver! " );}  Catch  (Exception e) {system. Out. Print ( "Error loading Oracle driver! " ); E. printstacktrace ();} Try  {Connection connect = Drivermanager. getconnection ( "JDBC: oracle: thin: @ localhost: 1521: orcl", "test", "test" ); System. Out. println ( "Success connect Oracle server! " ); Statement stmt = Connect. createstatement (); resultset rs = Stmt.exe cutequery ("select * From userinfo" );  While  (Rs. Next () {system. Out. println (Rs. getstring ( "Username"));}}  Catch  (Exception e) {system. Out. Print ( "Get data error! " ); E. printstacktrace ();}}} 

Through the above Code, we found that it is similar to the Code instance connecting to the MySQL database. The differences are basically the following:

1. The name of the loaded driver is different. Oracle loads the driver oracle. JDBC. Driver. oracledriver and MySQL loads the driver com. MySQL. JDBC. Driver.
2. The URL for connecting data is different. The URL for connecting to the Oracle database is JDBC: oracle: thin: @ localhost: 1521: orcl, And the URL for connecting to the MySQL database is JDBC: mysql: // localhost: 3306/test.

Note: Create a data name named test in the MySQL database, and create a table named userinfo. In Oracle, the user test is created in the default database orcl, and the table userinfo is created under the user test.
3.2the above three methods for parsing myoraclejdbc instances can also be used to load the driver. What happens if we don't load the driver like the previous mysqljdbc instance. Suppose we comment out all the statements for loading the driver, the Code is as follows: View code

 Import  Java. SQL. connection;  Import  Java. SQL. drivermanager;  Import  Java. SQL. resultset;  Import  Java. SQL. statement;  Import  Oracle. JDBC. Driver. oracledriver;  Public   Class  Myoraclejdbc {  Public  Static   Void  Main (string ARGs []) {  Try  {Connection connect = Drivermanager. getconnection ( "JDBC: oracle: thin: @ localhost: 1521: orcl", "test", "test" ); System. Out. println ( "Success connect Oracle server! " ); Statement stmt = Connect. createstatement (); resultset rs = Stmt.exe cutequery ("select * From userinfo" ); While  (Rs. Next () {system. Out. println (Rs. getstring ( "Username" ));}}  Catch  (Exception e) {system. Out. Print ( "Get data error! " ); E. printstacktrace ();}}} 

If you execute this program again, an error occurs. The error message is as follows:

 
Java. SQL. sqlexception: no suitable driver found for JDBC: oracle: thin: @ localhost: 1521: orcl at java. SQL. drivermanager. getconnection (drivermanager. java: 602) at java. SQL. drivermanager. getconnection (drivermanager. java: 185) at Edu. SJTU. erplab. JDBC. myoraclejdbc. main (myoraclejdbc. java: 33) Get data error!
 

This means that you must load oracle. JDBC. Driver. oracledriver to the JVM before establishing a database connection.

Question: I do not understand why MySQL database connection is not required Class. forname ("com. MySQL. JDBC. Driver "); 4. the user table of the demo database in the above test case

 Create   Table  'Userinfo' ('userid'  Int ( 10 ) Not   Null  Auto_increment, 'username'  Varchar (20 ) Default   Null  ,  Primary   Key  ('Userid') Engine  = InnoDB auto_increment =  3   Default Charset =  Utf8;  --  ---------------------------- --  Records of userinfo  --  ----------------------------  Insert   Into Userinfo Values ( '  1  ' , '  Zhangsan  '  );  Insert   Into UserinfoValues ( '  2  ' , '  Lisi  ' );

5.org. gjt. Mm. MySQL. Driver and COM. MySQL. JDBC. Driver

From mysqljdbc. Java, we can see two ways to load the MySQL JDBC driver:

 
Class. forname ("Org. gjt. Mm. MySQL. Driver");// Method 1: load the MySQL JDBC driverClass. forname ("Com. MySQL. JDBC. Driver");// Method 2: load the MySQL JDBC driver
 

The two methods can load the MySQL JDBC driver. What is the difference? ActuallyOrg. gjt. Mm. MySQL. DriverIs an early MySQL JDBC driver, but it is not a MySQL company, MySQL later received the JDBC driver of mm as the official JDBC driver, and renamed itCom. MySQL. JDBC. DriverIn the latest MySQL JDBC driverOrg. gjt. Mm. MySQL. DriverThe reference of this path, but in factOrg. gjt. Mm. MySQL. DriverCalledCom. MySQL. JDBC. DriverSo there is no difference between the two drivers.

View code

 //  Org. gjt. Mm. MySQL. DriverSource code  Import  Java. SQL. sqlexception;  /**  * Here For backwards compatibility with MM. MySQL **  @ Author Mark donews  */  Public   Class Driver Extends  Com. MySQL. JDBC. Driver {  //  ~ Constructors  //  -----------------------------------------------------------  /**  * Creates a new instance of driver **  @ Throws  Sqlexception * If a database error occurs. */    Public Driver () Throws  Sqlexception {  Super  ();}}