Connection Methods and differences between Java and SQLServer, MySql, Oracle, and Access

I. overview 1.1JDBC concept JDBC (JavaDatabaseConnectivity) is a user interface for connecting to the database provided by Java to support SQL functions. JDBC includes a set of APIS (Java) language-written interfaces and classes are independent of specific DBMS, or they can be associated with various databases. 1.2JDBC uses JDBC from 1

I. overview 1.1 JDBC concept JDBC (Java Database Connectivity) is a user interface for connecting to databases provided by Java to support SQL functions. JDBC includes a set) language-written interfaces and classes are independent of specific DBMS, or they can be associated with various databases. 1.2 JDBC purpose: JDBC is composed

I. Overview

1.1 JDBC Concept

JDBC (Java Database Connectivity) is a user interface provided by Java to connect to databases to support SQL functions) language-written interfaces and classes are independent of specific DBMS, or they can be associated with various databases.

1.2 JDBC usage

JDBC is composed of classes and interfaces written in a group of Java languages. It uses embedded SQL statements to implement three functions: database links, SQL statements, and SQL Execution results; JDBC supports basic SQL functions, which can be used to establish connections with different relational databases and perform related operations without having to write programs for different DBMS.

Before the emergence of JDBC, the only way to access the database through Java is to access the CGI program at the iruwuqi end using the Java input/output stream, access and operate the database through the CGI program, and obtain the result. Each access request starts a new CGI process on the Web server to establish a connection with the database. In this way, the access efficiency and speed are low and errors are easy to introduce.

1.3 JDBC structure and features

To put it simply, JDBC can accomplish the following three tasks:

• Establish a connection with a database;
• Send SQL statements to the database;
• Process the results returned by the database.

JDBC consists of two APIs independent from databases: JDBC APIs for program developers and JDBC Driver APIs for underlying layers.

The JDBC APIs for program developers are described as aggregated abstract Java interfaces through which applications can open connections to a database, execute SQL statements, and process the structure. The most important interfaces are as follows:

• Java. SQL. DriverManager: handles incoming driver calls and provides support for new database connections.
• Java. SQL. Connection: Connection to a specific database of the team
• Java. SQL. Statement: indicates a specific container to execute SQL statements on a specific database.
• Java. SQL. ResultSet: Controls data access to a specific statement.

Java. SQL. Statement has two sub-classes: java. SQL. PreparedStatement, which is used to execute pre-compiled SQL statements and java. SQL. CallableStatement, which are used to call the embedded process of a database.

The underlying JDBC Driver API is mainly used by database manufacturers to develop underlying database drivers. Generally, programmers who are used to develop applications cannot use these libraries. Java application programmers operate databases through a series of abstract classes defined in the SQL package, and the actual operations to implement these abstract classes are performed by the database Driver.

1.4 JDBC and other APIs

In addition to processing JDBC, Microsoft ODBC may be the most widely used API for accessing relational databases, or even more widely used than JDBC, it provides the ability to connect to almost any platform and any database. Of course, you can also directly Use ODBC in java, with the help of JDBC, with the JDBC-ODBC bridge implementation. But why JDBC?

• ODBC is not suitable for direct use in Java.
• It is not satisfactory to fully implement the JDBC translation from the C-code ODBC to the Java API.
• ODBC is easy to learn. It combines simplicity with complexity ,.
• Java APIs such as JDBC are required for pure Java solutions.

Version is the Platform Declaration:JDK 1.7

My eclipse 7.5

Access 2007

SQL Server 2008

For MySql 5.1

Oracle 9i

Platform Windows XP SP3

II. Java and Access Database (Access2007)

2.1 load driver

Introduce the corresponding class and package, use JDBC source program need to input java. SQL package, when necessary also need to load the JDBC-ODBC driver package:

Import sun. jdbc. odbc. JdbcOdbcDriver introduce javax. SQL if necessary.

2.2 load data sources

The so-called data source is the database file created with Access. Assume that the source name is IProducts. mdb. Follow these steps:

Control Panel -- Administrative Tools -- Data Source (ODBC) (2.1). 1

Open the ODBC Data Source Administrator dialog box -- select the system DSN (the user DSN is not selected, because the system DSN can avoid permission issues during debugging), and then "add ", on the displayed page, select Microsoft Access Driver (with 2007 installed, you can select *. mdb ,*. accdb) (2.2)

. 2

"Done" -- "select"

. 3

Select the database file to be added on the right side, and enter "IProducts" in "data source name" (this option is left blank, but remember what will be used later ).

2.3 code implementation

CODE:

Public boolean linkByUsingAccess (){
Statement stat = null;
Connection con = null;
ResultSet rs = null;
Try {
// Load the JDBC driver
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
// Create a database connection
Con = DriverManager. getConnection ("jdbc: odbc: IProducts ","","");

// No password is set, so leave the user name and password Blank

String SQL = "select * from devil_lang_map ";
Stat = con. createStatement ();
Rs = stat.exe cuteQuery (SQL );
Int I = 97;
While (rs. next ()){
String value = rs. getString ("human ");
TreeMap. put (char) I ++) + "", value );
}
} Catch (Exception e ){
E. printStackTrace ();
Return false;
} Finally {
Try {
If (rs! = Null ){
Rs. close ();
}
If (stat! = Null ){
Stat. close ();
}
If (con! = Null ){
Con. close ();
}
} Catch (Exception e2 ){
E2.printStackTrace ();
Return false;
}
}
Return true;
}

2.4 note

2.4.1 When the driver is loaded (Class. forName () is executed), the driver automatically registers itself with JDBC. Note that the Class. forName () method may throw a ClassNotFoundException exception, so this exception will be thrown when the driver Class may not exist.

2.4.2 the standard method for opening a connection to the database to establish a connection is to call DriverManager. getConnection. This method receives a string containing a URL. The DriverManager class (the so-called JDBC management layer) will try to find the driver that can be connected to the database represented by that URL. The DriverManager class contains a list of registered Driver classes. When the getConnection method is called, it checks every driver in the list and finds the driver that can be connected to the database specified in the URL, the getConnection method of Driver Manager uses this URL to create a municipal link.

2.4.3 the standard syntax of a jdbc url consists of three parts, which are separated by colons. For example, jdbc: <子协议> : <子名称>

• Jdbc-protocol. The protocol in the jdbc url is always JDBC;
• <子协议> -- Driver name or database connection mechanism (one or more drivers are supported ). A typical example of a sub-protocol name is odbc, which is specially reserved for URLs used to specify ODBC-style data resource names. For example, to access a database over a JDBC-ODBC bridge.
• <子名称> -- A method to identify a database.

III. Java and SQL Server database (SQL Server2008)

(Note: For convenience, the following SQL Server should be replaced by SS)

3.1 connection code and Statement Analysis

3.1.1 important statement Functions

Class. forName ("xxx ");

JDBC already has multiple drivers. In this case, traverse the registered drivers in the system until a correct driver is found.

Connection con = DriverManager. getConnection (url );

If the connection cannot be obtained, the program throws an exception. If the program has been executed here, it indicates that the connection has been successful.

3.1.2 code

CODE:

Public void testSQLServer (){
Connection con = null;
Statement stat = null;
ResultSet rs = null;
Try {
Class. forName ("com. microsoft. sqlserver. jdbc. SQLServerDriver ");
// DriverManager. setLogStream (System. out );
String url = "jdbc: sqlserver: // localhost: 1492; databasename = javaDB ";
String user = "JavaDB ";
String password = "8316051 ";
Con = DriverManager. getConnection (url, user, password );
Stat = con. createStatement ();
String SQL = "select * from currentdb ";
Rs = stat.exe cuteQuery (SQL );
While (rs. next ()){
System. out. println ("DB_Name:" + rs. getString ("NAME "));
System. out. println ("DB_Corporation:" + rs. getString ("CORPORATION "));
}
} Catch (Exception e ){
E. printStackTrace ();
} Finally {
Try {
If (rs! = Null ){
Rs. close ();
}
If (stat! = Null ){
Stat. close ();
}
If (con! = Null ){
Con. close ();
}
} Catch (Exception e1 ){
E1.printStackTrace ();
}
}
}

3.2 SQL Server 2008 error resolution and MyEclipse connection SS exception resolution

3.2.1 driver guide

First, download the driver from the official website: http://www.microsoft.com/downloadS/details.aspx? Familyid = 99B21B65-E98F-4A61-B811-19912601FDC9 & displaylang = zh-cn

This driver also supports SS2000, SS2005, and SS2008.

Right-click the project and choose -- Properties -- Java Build Path -- Libraries -- Add External JARs... Then select the jar package.

The driver contains two jar packages: sqljdbc. jar and sqljdbc4.jar.

3.1 and 3.2 after loading respectively

. 1

. 2

However, the following error occurs when you connect to the database after loading sqljdbc. jar: (. 3)

. 3

Later, sqljdbc. jar was removed (the removal method is the same as the loading method, but the selection is different). To load sqljdbc4.jar, you only need to import the package to connect to the SS database without explicitly importing the package. (3.4)

. 4

3.2.2 MyEclipse exception

Message: com. microsoft. sqlserver. jdbc. SQLServerException: failed to connect to the TCP/IP of the host localhost through port 1368. Error: "Connection refused: connect. Verify the Connection Properties, check that the SQL Server instance is running on the host, accept the TCP/IP connection on this port, and ensure that the firewall does not block the TCP connection to this port .".

Solution:

• Check the driver connection statement "con = DriverManager. getConnection (url, user, password);" whether the port number in the sentence is consistent with the TCP port set in SQL Server Configuration Manager;
• Enter "telnet localhost port" in cmd to check whether a port is enabled. If you are prompted that the port cannot be enabled, the port cannot be enabled for some reason. Disable the TCP/IP service and restart it;
• Restart the SQL Server service (the Express version is displayed as SQL Server (Express )).

Message: com. microsoft. sqlserver. jdbc. SQLServerException: the user 'javadb' fails to log on.

Analysis: The program can run here to prove that the database is loaded correctly.

Solution:

• The user name or password is incorrect;
• This user does not exist in the database.

3.2.3 SS database error:

Message: A connection has been established with the server, but an error occurs during logon. (Provider: Shared Memory provider, error: 0-there is no process on the other end of the MPs queue .)

Solution:

• Choose SERVER (right-click)> Properties> Security> SERVER Authentication To "SQL SERVER and WINDOWS Authentication mode ";
• Expand "security"-> login name-> select the SA Login Account (right-click)-> Status-> login to enable;
• Restart the SQL Server service. (Note: A restart is required to take effect );
• Restart Microsoft SQL Server Management Studio (not required ).

Problem description: The default database cannot be opened during login

Solution:

This issue is related to the previous issue. No "User ing" database is set. Log on with another available user, select "security"> "Login Name"> "User ing", and select a database.

Problem description: After logging on to the database with the newly created account, only the system database is found, and the system prompts "data cannot be retrieved for this request"

Solution: use an account with sysadmin permissions to log on, and right-click the new user name in "security"-"login name, in the displayed "Logon properties" dialog box, select "server role" and select "sysadmin ". Refresh the new user and you will see other databases. If you want to set the owner of a new user, select "User ing" in the "Login properties" dialog box, select the database to be set, and select "db_owner" below.

3.2.4 create a new account

In an account with sysadmin permissions, right-click "security"> "Login Name", enter the user name in the username column, and select Windows authentication or SQL Server Authentication, enter the password (not required for Windows Authentication), cancel the Password Expiration item, select the database and language at the bottom, and then confirm.

IV. Java and Oracle databases

4.1 load driver

The driver is located:

Oracle installation directory -- Ora92 -- lib -- ojdbc14.jar

Note: If the driver is not loaded, the exception is: java. lang. ClassNotFoundException: oracle. jdbc. driver. OracleDriver.

4.2 connection code

CODE:

Public Oracle (){
Connection con = null;
Statement stat = null;
ResultSet rs = null;
Try {
Class. forName ("oracle. jdbc. driver. OracleDriver ");
String url = "jdbc: oracle: thin :@ localhost: 1521: Jason ";
String user = "scott ";
String password = "tiger ";
Con = DriverManager. getConnection (url, user, password );
Stat = con. createStatement ();
String SQL = "select * from emp ";
Rs = stat.exe cuteQuery (SQL );
While (rs. next ()){
System. out. println (rs. getString (1 ));
}
} Catch (Exception e ){
E. printStackTrace ();
} Finally {
Try {
If (rs! = Null ){
Rs. close ();
}
If (stat! = Null ){
Stat. close ();
}
If (con! = Null ){
Con. close ();
}
} Catch (Exception e2 ){
E2.printStackTrace ();
}
}
}

V. Java and My SQL

5.1 load driver

Load drive: mysql-connector-java-5.1.5-bin.jar

Code 5.2

CODE;

Public testMySql (){
Connection con = null;
Statement stat = null;
ResultSet rs = null;
Try {
Class. forName ("com. mysql. jdbc. Driver ");
String url = "jdbc: mysql: // localhost: 3306/datang ";
String user = "root ";
String password = "admin ";
Con = DriverManager. getConnection (url, user, password );
Stat = con. createStatement ();
String SQL = "select * from t_admin ";
Rs = stat.exe cuteQuery (SQL );
While (rs. next ()){
System. out. println (rs. getString (1 ));
}
} Catch (Exception e ){
E. printStackTrace ();
} Finally {
Try {
If (rs! = Null ){
Rs. close ();
}
If (stat! = Null ){
Stat. close ();
}
If (con! = Null ){
Con. close ();
}
} Catch (Exception e2 ){
E2.printStackTrace ();
}
}
}