Database Technology-JDBC connection to MySQL

Database Technology-JDBC connection to MySQLAbstract: JDBC (Java DataBase Connectivity, java DataBase connection) is a Java API used to execute SQL statements. It can provide unified access to multiple relational databases, it consists of a group of classes and interfaces written in Java. JDBC provides a standard API for database developers to build more advanced tools and interfaces so that database developers can use pure Java APIs to write database applications, it can run across platforms and is not restricted by database vendors.
1. cross-platform running:This inherits the "one-time compilation, running everywhere" feature of the Java language;
2. not restricted by database vendors:Cleverly, JDBC has two interfaces. One is for the application layer, which enables developers to call the database and process results through SQL without considering the database provider; the other is the driver layer, which processes the interaction with a specific driver. The JDBC driver can use the jdbc api to create a bridge between the Java program and the data source. The application can be moved to various drivers only once. Sun provides a driver Manager. The drivers provided by the database vendors, such as MySQL and Oracle, can be identified and work properly if they meet the requirements of the driver manager. Therefore, JDBC is not restricted by database vendors.
JDBC APIs can be used as links to connect Java applications to various relational databases, which brings convenience and negative effects. The following are the advantages and disadvantages of JDBC.Advantages:
Easy to operate:JDBC eliminates the need for developers to use complex drives to call commands and functions;High Portability:JDBC supports different relational databases, so the same application can support access from multiple databases, as long as the corresponding driver is loaded;Good versatility:The JDBC-ODBC bridge driver replaces the JDBC function with ODBC;Object-oriented:Common JDBC database connections can be encapsulated into a class and called directly when used.

Disadvantages:

The speed of accessing data records is affected to a certain extent; it is difficult to change the data source: JDBC supports multiple databases, and operations between different databases may vary, which makes it very difficult to change the data source.

Keywords:JDBC database MySQL
 

Database Technology-JDBC connection to MySQL

1. process and principle of connecting to the database through JDBC

1,Load the driver of the specified database in the development environment.

MySQL supports JDBC driver is: mysql-connector-java-5.1.18-bin.jar)

2,Load the driver in a Java program. In Java programs,

Class. forName ("specify the database driver ")

Class. forName ("com. mysql. jdbc. Driver ")

3,Create a data connection object

Connection connection = DriverManager. getConnection ("URL to connect to the Database", "username", "password "). URL = protocol name + IP address (Domain Name) + port + database name; user name and password are the user name and password used to log on to the database.

Connection connect =

DriverManager. geiConnection ("jdbc: mysql: // localhost: 3306/DatabaseName", "root", "123 ")

4,Create a Statement object

The Statement class is mainly used to execute static SQL statements and return the objects of the results it generates.

Statement statament = connection. createStatement ();

Statement statamentMySQL = connectMySQL. createStatement ();

5,Call the related methods of the Statement object to execute the corresponding SQL Statement.

Statement. excuteUpdate ("INSERTINTO table (name, age, sex, address, depart, worklen, wage)" + "VALUES ('tom1', 321, 'M', 'China ', 'personnel ', '3', '123 ')");

You can call the executeQuery () method of the Statement object to query data. The result of the query gets the ResulSet object. ResulSet indicates the set of data returned after the database is queried, the ResulSet object has a pointer that can point to the current data row. Use the next () method of the object to direct the pointer to the next row, and then retrieve the data by column number or field name. If the next () method returns null, it indicates that no data exists in the next row. The sample code is as follows:

ResultSet resultSel restore statement.exe cuteQuery ("select * from table ");

6,Close database connection:

Close () method of Connection to close the data Connection in time.

Finally{

Try{

If(Rs! =Null){

Rs. close ();

}

If(Stmt! =Null){

Stmt. close ();

}

If(Conn! =Null){

Conn. close ();

}

}Catch(Exception e2 ){

System.Out. Println (e2.toString ());

}

}

Ii. Demonstration

First, prepare the MySQL database

1. We need to know the MySQL url user password

§ Note: view by: mysql> show variables

§

§ Or you can see the MySQL configuration of the local machine at a glance through other MySQL auxiliary software. Here I am using mysql workbench

§

§

§ View my

§ Ipurl: localhost3308

§ User: root

§ Password: 123

§ The localhost of the url is the ip address of the database, and the local mysql is also at 192.168.1.115 in the LAN (that is, the local ip address)

§ View method: cmd-> ipconfig

§

 

 

2. Open MySQL to create a database and prepare for jdbc connection

§ It indicates that our jdbc connection needs to be connected to a database of a specific address.

§ Format reference Connection connect =

DriverManager. geiConnection ("jdbc: mysql: // localhost: 3306/DatabaseName", "root", "123 ")

 

Next, create a database,

Create with MySQL:

I have created a database named zoo and can connect to java jdbc.

1. First, import the jdbc driver jar of mysql,

A) create a lib folder under the project and put it in the jar file of mysql-connector-java.

B)

C) Add jars to Add jar

D)

Code Writing

Package myjdbc; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. SQL. SQLException; import java. SQL. statement; public class Connn {public static void main (String [] args) throws SQLException {String user = "root"; String password = "123"; String url = "jdbc: mysql: // localhost: 3308/zoo "; String url2 =" jdbc: mysql: // 192.168.1.115: 3308/zoo "; String url3 =" jdbc: mysql: // localhost: 3308/zoo? Users = root & password = 123 & useUnicode = true & characterEncoding = 8859_1 "; String driver =" com. mysql. jdbc. driver "; String tableName =" animal "; String sqlstr =" insert into animal (id, name, year) value ("+ 5 +", "+" aiii "+ ", "+ 33 +"); "; Connection conn = null; Statement stmt = null; ResultSet rs = null; try {Class. forName (driver); conn = DriverManager. getConnection (URL 3, "root", "123"); stmt = conn. createStatement (); System. Out. println ("database connection successful! ");} Catch (ClassNotFoundException e) {System. out. println (" the database driver does not exist! "); System. out. println (e. toString ();} catch (SQLException e) {System. out. println ("SQL error"); System. out. println (e. toString ();} finally {try {if (rs! = Null) {rs. close () ;}if (stmt! = Null) {stmt. close ();} if (conn! = Null) {conn. close () ;}} catch (Exception e2) {System. out. println (e2.toString ());}}}}

 

Connection successful!

Iii. Summary

The Jdbc connection to the database is not complex, and the connection mode has been clearly defined.

Class. forName ("com. mysql. jdbc. Driver")->

DriverManager. getConnection->

CreateStatement->

Rs operations

You can proceed smoothly.

Note: DriverManager. getConnection is followed by many statements.

1. StringUrl= "Jdbc: mysql: // localhost: 3308/zoo ";

2. StringUrl2= "Jdbc: mysql: // 192.168.1.115: 3308/zoo ";

3. String url3 = "jdbc: mysql: // localhost: 3308/zoo? User = root & password = 123 & useUnicode = true & characterEncoding = 8859_1 ";

These are all possible, mainly because the port should not be wrong. Generally, there is no problem!

Provide some help files:

Mysql-essential-5.6.0 http://download.csdn.net/detail/zhangty0223/6811589

Provide a learning material:

Mysql learning from scratch part1: http://download.csdn.net/detail/zhangty0223/6811667

Mysql learning from scratch part2: http://download.csdn.net/detail/zhangty0223/6811695

Java jdbc mysql DRIVER: http://download.csdn.net/detail/zhangty0223/6819387

The entire project source code: http://download.csdn.net/detail/zhangty0223/6819457

Thank you!

2014.1.7