Shulou(Shulou.com)06/01 Report--

This article mainly introduces the application of JDBC driver in java connection mysql, which involves things, learned from the theoretical knowledge, there are many books, literature for your reference, from the practical point of view, accumulated years of practical experience can be shared with you.

Summary: a JDBC driver is required to connect to mysql through java. The JDBC driver is installed on the mysql-client client, and the mysql-server server does not need to be installed. Database installation initialization strategy. The commands used in the test:

1. Create test libraries, tables, fields:

Create database score

Use score

Create table score (id nvarchar (10), stu_id nvarchar (10), c_name nvarchar (10), grade nvarchar (10))

Insert into score values (15, 10, 14)

2. Authorize remote access:

Mysql > grant ALL PRIVILEGES ON *. * to root@ "%" identified by "mysql"

Mysql > FLUSH PRIVILEGES

Java installation outline, environment variable setting:

# 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

For more information on JDBC download, please see the mysql official website. Installation is the setting of environment variables:

Method one

# vim / etc/profile

Export CLASSPATH=.:/devops/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar

Method two

For java applications such as tomcat, you should refer to the official documentation to see how to install the driver, such as decompressing it and placing it under {$TOMCAT} / lib.

Method three

Put it in the lib directory under the JDK installation directory (it can also be another location)

For example, if my JDK is installed in the / usr/java/jdk1.7.0_71 directory, then I will 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 path of mysql-connector-java 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:

Compilation tool preparation

Javac,java . Just install the java-devel package.

Compile

Javac DB.java

Running

Java DB

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://10.1.37.24:3306/score"

String user = "root"

String passwd = "123456"

String sql1 = "SELECT * FROM score"

String sql2 = "INSERT INTO score VALUES (25.9205pr. Englis);"

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 (rs.next ()) {

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 executed successfully!")

}

Int j = stat.executeUpdate (sql3)

If (j! = 0) {

System.out.println ("UPDATE statement executed successfully!")

}

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 ("Database driver not found!")

} catch (SQLException e) {

System.out.println ("failed to connect to database cloud server!")

}

}

}

=

Expansion: the difference between JDBC and ODBC:

Both JDBC and ODBC can achieve similar functions, but the difference between JDBC and ODBC is that they have different development architectures and different implementation details.

When it comes to the difference between JDBC and ODBC, JDBC and ODBC are actually initiators used to connect to the database. The Chinese name of ODBC is called Open Database Interconnection, which is a part of the database in the open service framework of Microsoft performance development. it establishes a set of relevant rules and helps a group of practical programming interfaces for database access. To put it simply, ODBC is a prop for the actual application program to stop interacting with the database system. A practical application program of ODBC does not rely on the database system to operate the database, and does not support dealing with the database management system. All database operations are completed by the corresponding database system's ODBC driver. As a result, no results appear to deal with all databases in the same way.

JDBC, similar to ODBC, is also an intermediary personal company where the actual application program stops communicating with the database. It's just that their developers are different. JDBC is from Sun personal company to the associated database system manufacturers to help JDBC specifications and requirements; then the major manufacturers follow the standard specifications to design JDBC drivers in line with their own database industry products. Although JDBC and ODBC do not have similar features, their development architectures are different and their appearance details are different. For this reason, database programming must understand the differences in this aspect, and choose the appropriate database driver according to the actual situation in the part-time job.

The difference between JDBC and ODBC: the advantages of JDBC.

JDBC practical application program interface is the program development interface for data access in JAVA program language, which is composed of many classes and interfaces. ODBC is developed by the C language. Due to the difference between the two development platforms, the respective characteristics of different kinds of development are transferred to the database startup program. According to the author's understanding, compared with the ODBC database startup program, JDBC has the following advantages. If the author's summary is not enough, you are welcome to complete it.

1. JDBC is easier to understand than ODBC. Those of you who have studied programming may have an intuitive impression that JAVA is much easier to learn than C. Because the design idea of JAVA language is object-oriented and close to people's cognitive thinking, it is easy to accept and relatively easy to study. On the other hand, C language is more abstract and has a definite distance from the rules of human cognition. For this reason, the industrial commodities they have developed also have similar characteristics. In ODBC, although there is no score to interact with the database, it is complicated to appear. Such as a simple query, also need to be divided into several pieces of content; and in the ODBC driver to stop the integration, stop some complex operations. This not only reduces the performance of database startup programs, but also brings definite negative effects for program developers to develop practical applications. The JDBC database startup program contains most of the basic data operation functions in the design time, so when writing some conventional database operation statements, such as query, update and so on, the source code is much less than ODBC. Therefore, in this respect, the JDBC database startup program is easier to understand than ODBC.

2. JDBC database driver is object-oriented and completely follows the excellent characteristics of JAVA language. In general, as long as there is a JAVA car function to design the basis of users do not achieve in the shortest possible time to understand the architecture of the JDBC driver, compared to easy to use, no results easy to develop a strong database application program. And ODBC, because of its complex internal functions, high requirements for writing source code. For this reason, even a master of the C language, it still takes a lot of time to understand the database startup program; in the time of writing the source code, it is inseparable from the relevant reference books.

3. The portability of JDBC is better than ODBC. In general, after the ODBC driver is installed, a certain configuration is required before it can be applied. Different configurations cannot be common between different database servers. That is to say, install the requirements configuration once. However, the JDBC database driver is different. If you use the JDBC database driver, you do not need to stop the additional configuration if you know that you need to select the appropriate JDBC database driver. During the installation process, the JDBC database driver completes the configuration on its own. For this reason, JDBC is more portable than ODBC.

In short, JDBC and ODBC are database launchers, they are essentially the same, are designed to deal with SQL statements. And JDBC in the design time, it is also on the basis of ODBC to stop the design, and retain some of the functions of the ODBC database driver. In other words, it's okay for us to think of JDBC as another advanced version of ODBC. JDBC has mainly made definite improvements in operation and friendliness.

The difference between JDBC and ODBC: when will JDBC be adopted?

Although JDBC database startup program has many advantages over ODBC, it does not mean that JDBC database startup program can play a good effect in all cases. Database programming also needs to stop the selection according to the actual application environment of the enterprise. Under normal circumstances, if any of the following situations are met, the author suggests that the JDBC database driver should be used.

One is to use the Oracle JDeveloper 10G of Oracle personal company to develop the practical application program. JDeveloper 10G is a visual development environment with the help of Oracle personal company. No achievements help database programming and development to facilitate the completion of some complex functions. For example, the database development failed to use that prop to design the web page running program of the practical application of WEB; if it did not achieve results, it was used to develop the business service layer components; if there was no achievement, the data binding function appeared in the actual application of JSP and JClient, and so on. Especially in the time of using that prop to develop business service layer components, I failed to directly browse and access the data corresponding to the business components. Database development does not have to wait until the actual application program is written before testing the access function of the database. Obviously, that feature makes it particularly convenient for database developers to develop the actual application of the database. In order to cooperate with that development prop, Oracle personal company has specially developed the corresponding JDBC driver. For this reason, if the database development uses JDeveloper to develop props, then the compatibility of using JDBC is better than using ODBC. So if you use this kind of development props, it is best to use JDBC database startup program.

Second, if the actual application program uses the JAVA development platform, then it is best to apply the JDBC database startup program. In fact, JDeveloper also uses the JAVA development platform. This is mainly because if the JAVA program directly calls the C language of ODBC in the actual application of the program interface, it is easy to produce security difficulties. For example, the Java language does not use pointers (because the processing efficiency of pointers is slow), while the C language used by ODBC uses a large number of pointers. For this reason, if you use the Java language platform to develop the actual application program of the database, if you use the 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. To this end, the author's opinion, if the database development is the JAVA language development platform, then it is best to use the JDBC driver, rather than the ODBC driver.

In more related cases, database programming and development do not achieve results according to their own habits to select the appropriate database driver.

The difference between JDBC and ODBC: can you make a smooth transition from ODBC to JDBC?

Maybe database programming used to use ODBC drivers, but if database programming now requires JDBC drivers, can there be a smooth transition? The correct answer is yes. There is a class of JDBC drivers called JDBC-ODBC bridging launchers. The underlying layer of this category of JDBC database driver is connected to the database through the ODBC driver. If the original actual application program is based on the ODBC database driver, or the database does not help the corresponding JDBC driver, then the database programming fails to use the JDBC-ODBC bridge driver to appear. That is to say, the bridge driver fails to use the existing ODBC driver to access the contact database. For this reason, they not only failed to retain the previous development architecture (to access data through ODBC), but also immediately applied JAVA as a new development environment, resulting in a smooth transition from ODBC database driver to JDBC.

However, when using this kind of bridging driver, you need to pay attention to several difficulties. One is that the bridge driver still needs to use the ODBC database driver. Because the object directly contacted by the bridge driver is the ODBC driver, and then access the database through the ODBC driver. To do this, the ODBC driver must be installed and configured on the client. If you are using a three-tier development framework, you also need to install ODBC drivers. Secondly, in this mode, the actual application first calls JDBC, then calls ODBC through JDBC, and finally stops communicating with the database. Obviously, there are a few more links in the middle. Because there are many intermediate links, but when there are difficulties in data access, it is not easy to find the difficulties. This is like a water pipe, if there are more connections in the middle, then the probability of water leakage is higher. If it is really in addition to leakage, then the time to query the leakage point will also be difficult. For this reason, the author thinks that the use of bridge-type JDBC drivers is only a stopgap measure. At the right time, database development or needs to adjust the original development architecture, all transferred to the JDBC driver. The bridge program only buys a certain amount of time for database development. Although the labor pains in the process of transformation are painful, they are inevitable. Long-term pain is better than short-term pain, the author believes that database development is better to stop the transition as soon as possible. And in the case of estimation, the previous development architecture is also stopped to adjust to adopt the real sense of the JDBC driver.

Read the above JDBC driver in the java connection mysql application introduction, I hope it can bring some help to everyone in the practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.