JAVA connection ACCESS, MYSQL, SQLSEVER, ORACLE Database _ MySQL

Source: Internet
Author: User
JAVA connection ACCESS, MYSQL, SQLSEVER, ORACLE Database . 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 JDBCDriver API is mainly used by database vendors to develop underlying database drivers. generally, programmers who are used to develop applications cannot use these class 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.

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"

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 booleanlinkByUsingAccess () {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. Therefore, leave the username and password blank. String SQL = "select * from devil_lang_map "; stat = con. createStatement (); rs initialize stat.exe cuteQuery (SQL); int I = 97; while (rs. next () {String valu E = rs. getString ("human"); treeMap. put (char) I ++) + "", value) ;}} catch (Exception e) {e. printStackTrace (); returnfalse;} 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.3JDBC URL standard syntax consists of three parts, each part is separated by a colon, such as 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 voidtestSQLServer() {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";Stringpassword = "8316051";con =DriverManager.getConnection(url, user, password);stat =con.createStatement();String sql ="select * from currentdb";rs =stat.executeQuery(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 ExternalJARs .. 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.2MyEclipse 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. Make sure that the correct JDBC driver is used (note version) 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 TCP/IP 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, restart it, and restart the SQL Server Service (The Express version is displayed as SqlServer (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.3SS 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: Only a restart is required to take effect.) restart Microsoft SQL Server ManagementStudio (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:publicOracle() {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";Stringpassword = "tiger";con =DriverManager.getConnection(url, user, password);stat =con.createStatement();String sql ="select * from emp";rs =stat.executeQuery(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;publictestMySql() {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";Stringpassword = "admin";con =DriverManager.getConnection(url, user, password);stat = con.createStatement();String sql ="select * from t_admin";rs =stat.executeQuery(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();}}}

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.