Connect MySQL with Java summary

Source: Internet
Author: User
Tags odbc

Summary: The JDBC driver is required to connect to MySQL via Java. The JDBC driver is installed on the Mysql-client client and the Mysql-server server does not need to be installed.
    • The database installation is initialized slightly. The command used to test:

1. Create test libraries, tables, fields:

Create DATABASE score;

Use score;

CREATE TABLE score (ID nvarchar (ten), stu_id nvarchar (ten), C_name nvarchar (+), Grade nvarchar (10));

INSERT into score values (15,10, ' 中文版 ', 14);

2. Authorized Remote access:

Mysql> Grant all privileges on * * to [email protected] '% ' identified by ' MySQL ';

Mysql>flush Privileges

650) this.width=650; "title=" 1 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" 1 "src=" Http:// 3yfshjaabfagzo0xw071.png "" 970 "height=" 277 "/>

    • Java installation slightly, environment variable settings:

# Vim/etc/profile

Export java_home=/usr/java/jdk1.7.0_79

Export path= $JAVA _home/bin: $PATH

Export classpath=.: $JAVA _home/lib/dt.jar: $JAVA _home/lib/tools.jar

650) this.width=650; "title=" 2 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" 2 "src=" Http:// 3w8x2haaaae7h5icu012.png "" 729 "height="/>

    • JDBC Download see MySQL official website, installation is the environment variable settings:

Method One

# Vim/etc/profile

Export classpath=.:/ Devops/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar

650) this.width=650; "title=" 3 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" 3 "src=" Http:// 7zdzypaaao7q7drjc647.png "" 853 "height="/>

Method Two

For Java applications such as TOMCAT, refer to the official documentation to see how to install the driver, such as unpacking under {$TOMCAT}/lib.

Method Three

Place it in the Lib directory under the JDK installation directory (or another location)

For example, my JDK is installed in the/usr/java/jdk1.7.0_71 directory, so I put it in the/usr/java/jdk1.7.0_71/lib directory

Then modify the value of the environment variable classpath, edit the/etc/profile file, add the Mysql-connector-java path at the end of the Classpath value, and use the colon: separate

Export classpath=.: $JAVA _home/lib/dt.jar: $JAVA _home/lib/tools.jar: $JAVA _home/lib/ Mysql-connector-java-5.1.18-bin.jar

    • Test:

Compile tool Preparation

Javac,java. Install the Java-devel package.




Java DB

650) this.width=650; "title=" 4 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" 4 "src=" Http:// 6ryzgmaab-kj3tyqm015.png "" 833 "height=" 467 "/>
    • Script:
    • ===================================================================================

Import java.sql.Connection;

Import Java.sql.DriverManager;

Import Java.sql.ResultSet;

Import java.sql.SQLException;

Import java.sql.Statement;

public class DB {

public static void Main (string[] args) {

String url = "Jdbc:mysql://";

String user = "root";

String passwd = "123456";

String SQL1 = "SELECT * from Score";

String sql2 = "INSERT into score VALUES (25,9205, ' 中文版 ', 84);";

String sql3 = "UPDATE score SET grade=80 WHERE id=25";

try {

Class.forName ("Com.mysql.jdbc.Driver");

Connection con = drivermanager.getconnection (url, user, passwd);

SYSTEM.OUT.PRINTLN ("Database connection succeeded! ");

Statement stat = con.createstatement ();

ResultSet rs = stat.executequery (SQL1);

while ( ()) {

int id = rs.getint ("id");

int stu_id = Rs.getint ("stu_id");

String course = rs.getstring ("C_name");

int grade = Rs.getint ("Grade");

System.out.println (id + "" + stu_id + "" + Course + "" + grade);


int i = stat.executeupdate (SQL2);

if (i! = 0) {

SYSTEM.OUT.PRINTLN ("INSERT statement execution succeeded!");


Int J = stat.executeupdate (SQL3);

if (J! = 0) {

SYSTEM.OUT.PRINTLN ("UPDATE statement execution succeeded!");


if (rs! = null) {

Rs.close ();

rs = null;


if (stat! = null) {

Stat.close ();

stat = null;


if (con! = null) {

Con.close ();

con = null;


} catch (ClassNotFoundException e) {

SYSTEM.OUT.PRINTLN ("No database driver found!") ");

} catch (SQLException e) {

System.out.println ("Failed to connect to database server! ");




    • ===================================================================================

650) this.width=650; "title=" 5 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" 5 "src=" Djlsdaacbbmsbv94770.png "" 992 "height=" 578 "/>

650) this.width=650; "title=" 6 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" 6 "src=" Wkiom1hgxact7lanaabq4mwgbiq464.png "" 927 "height=" 591 "/>

    • Development: The difference between JDBC and ODBC:

Both JDBC and ODBC can achieve similar functionality, but the difference between JDBC and ODBC is that their development architectures differ in their implementation details.

When it comes to the difference between JDBC and ODBC, both JDBC and ODBC are actually the startup programs used to connect to the database. The ODBC Chinese name, called Open Database Interconnect, is a component of the database in the Open Services Framework for Microsoft Performance Development, which establishes a set of rules and helps a set of practical programming interfaces for database access. Simply put, ODBC is a prop that actually uses the program to stop interacting with the database system. A database system that gives ODBC practical application to database operation, does not support dealing with database management system, and all database operations are done by the ODBC driver of the corresponding database system. Thus no results appear in the same way to process all the databases.

JDBC, like ODBC, is also an intermediary personal company that actually uses the program to stop communicating with the database. But their developers are not the same. JDBC is a specification and requirement for JDBC from a sun personal company to a contact database system vendor, and then each major vendor follows the compliance specification to design a JDBC driver that conforms to its own database industry commodity. Although JDBC and ODBC do not have similar features, their development architectures are different, and the details of their appearance vary. For this database programming must understand this difference, and in the job according to the actual situation to select the appropriate database driver.

JDBC with the ODBC The difference: JDBC the advantages.

JDBC the actual application of the program interface is JAVA Programming language for data access involved in the development of the interface of the program, which is composed of many classes and interfaces. and ODBC is developed by the C language. Because the development platform of the two different, the development of different kinds of characteristics are also passed to the database startup program. According to the author's understanding, there are several advantages of JDBC relative to the ODBC Database Launcher program. If the author summarizes not all aspects, welcome to fill the whole.

1 , JDBC to be more than ODBC easy to understand. Learning programming may have an intuitive feeling that the Java language is more studious than the C language. Because the Java language design idea is object-oriented, with the people's understanding of the thinking of the close, the contest is easy to accept, the study is relatively simple. And the C language is the contest abstraction, with the person's understanding rule has the definite distance. For this reason they have developed a similar feature of the industrial goods. In ODBC, although there is no result of the interaction with the database, but the emergence of a complex contest. such as a simple query, also needs to be divided into several pieces of content, and within the ODBC driver to stop the integration, stop some complex operations. This not only reduces the performance of the database launcher, but also provides a definite negative effect for program developers to develop practical applications. While the JDBC database launcher in the design time contains most of the basic data manipulation functions, in order to write some general database operation statements, such as query, update, and so on, it requires much less source code than ODBC. Therefore, the JDBC database startup program is easier to understand than ODBC.

2 , JDBC database drivers are object-oriented and fully follow JAVA the excellent characteristics of language. usually, as long as there is a Java car function needs to design the basis of the user did not score in the shortest time to understand the architecture of the JDBC driver, the contest is easy to get started, no results easily developed a strong database of practical application procedures. In the case of ODBC, because of its complex internal functions, the source code is required to write high. For this reason even a master of C language, still need to spend a lot of time to understand the database startup program, in the time of writing source code, but also inseparable from the relevant reference book.

3 , JDBC is more portable than ODBC better. Typically, after you install the ODBC driver, you need a certain configuration to apply. A different configuration cannot be common between different database servers. That is to say, install a demand configuration once. However, the JDBC database driver is not the same. If the JDBC database driver is used, the need to select the appropriate JDBC database driver does not require an additional configuration to be stopped. During the installation process, the JDBC database driver completes its own configuration. For this reason, JDBC is better ported than ODBC.

anyway JDBC with the ODBC Are the start-up programs of the database, and they are essentially the same, all to handle SQL statements are designed. and JDBC at design time, it is also on the basis of ODBC to stop design, and preserves the ODBC database driver part of the function. Or, we have no grades. It is also a must to think of JDBC as another advanced version of ODBC. JDBC has made certain improvements in its operational and friendly nature.

JDBC with the ODBC the difference: What time is used JDBC?

Although the JDBC Database launcher has a lot of advantages over ODBC, it does not mean that the JDBC Database launcher can be a good result in all cases. The database programming also needs according to the enterprise actual actual application environment to stop the selection. In general, if any of the following conditions are met, the author views the JDBC database driver.

one is to use Oracle of individual companies Oracle JDeveloper 10G to develop practical application procedures. the JDeveloper 10G is a visual development environment for Oracle Personal company help. No grades. Help with database programming and development easy to complete a number of complex functions. such as database development does not have the performance of the props to design the Web-based application of the application of the Web page running program, such as the failure to develop business services layer components, such as no results in the JSP and jclient actual application of the data binding function, and so on. In particular, when using that prop to develop the business service layer components, there is no performance to directly browse and access the data corresponding to the business components. Database development does not have to wait until the actual application of the program is written to test the database access function. Obviously that feature makes database development particularly handy when developing a database to actually use the program. In order to match that development, the Oracle Personal company developed the corresponding JDBC driver specifically. For this reason, if the database is developed using JDeveloper development props, then using JDBC is better than using ODBC compatibility. So if you use this kind of development props, it is best to use the JDBC database startup program.

Second, if the actual application of the procedure is to use JAVA development platform, then the best application JDBC Database startup program. in fact, JDeveloper uses Java development platform. This is mainly because if the Java program directly calls the ODBC C language actually uses the program interface, the contest simplicity produces the security aspect difficulty. As the Java language does not take pointers (because of the slow processing efficiency of pointers), the C language used by ODBC uses a large number of pointers. For this reason, if the application of the Java language platform to develop the actual application of the database, if the use of ODBC database driver, it is not so appropriate. In addition, because JDBC is also developed in the Java language, its compatibility will be much better. For this reason, if the database is developed using the Java language development platform, it is best to use the JDBC driver instead of the ODBC driver.

In more relevant cases, database programming and development did not score according to their own habits to choose the appropriate database driver.

JDBC with the ODBC The difference: Can the ODBC smooth transition to JDBC?

Perhaps the database program used to be the ODBC driver, and if the database programming needs to use the JDBC driver, then can there be a smooth transition? The correct answer is yes. There is a class called Jdbc-odbc Bridge Launcher in the JDBC driver. The JDBC database driver for this category is the bottom layer of the ODBC driver to connect to the database. If the original application is based on the ODBC database driver, or the database does not help the corresponding JDBC driver, then the database programming does not use the Jdbc-odbc Bridge driver to appear. That means the bridge driver didn't score. Use the existing ODBC driver to access the contact database. For this reason, not only did the results retain the previous development architecture (via ODBC to access the data), but there was no result. Immediately apply Java as a new development environment, resulting in a smooth transition from ODBC database driver to JDBC.

However, in the time of using this bridge driver, the need to pay attention to a few difficulties. One is that the bridge driver still needs to use the ODBC database driver. Because the bridge driver directly contacts the ODBC driver, it then goes through the ODBC driver to access the database. To do this, the ODBC driver must be installed and configured first on the client. If you are using a three-tier development framework, you will also need to install the ODBC driver. Second, in this mode, the actual application of the program calls JDBC, and then through the JDBC call ODBC, and finally stop communicating with the database. Apparently there are a few more links between them. Because of its middle-link contest more, but the data access difficulties in the time, it is not how to check the difficulties. This is like a pipe, if the middle of the interface more, then in addition to the probability of water leakage is high. If there is really a leak, then the time to query the leak will also be difficult to compete. For this reason, I think the JDBC driver with bridging category is only expedient. At the right time, database development or the need to adjust the original development architecture, all go to the JDBC driver. The bridging program is just a time-out for database development. Although the throes of the transformation process is painful, it is inevitable. Long pain is not as short as pain, the author of the opinion database development or early stop transition as well. And, in the case of estimation, the previous development architecture was also stopped to use the true JDBC driver.

Connect MySQL with Java summary

Related Article

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: 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.