Sample Code for connecting to mysql database in myeclipse, myeclipsemysql

Sample Code for connecting to mysql database in myeclipse, myeclipsemysql

1. Environment Configuration

: Http://www.mysql.com/downloads/mysql/ is really troublesome, download also need to register and login and fill out a table. The above information is quite complete, and it is acceptable to fill out the information ~~ After the download, follow the prompts to install and finally set the password for logging on to mysql. After the installation is complete, test the connection to the database. Click "start"-"MYSQL5.5 Command Line cilent" in the program. after entering the password you just set, you can connect to the mysql server.

In the installed package, you can find a jar package in mysql_server \ Connector j xxxx. This jar package is required for java programs to connect to the mysql database. If this package does not exist, the program code will prompt: ClassNotFoundExceptioncom. mysql. jdbc. Driver error.

Create a new java project and create a new folder in the project that stores the jar package (such as lib), copy the mysql-connector-java-X.X.X-bin.jar to the folder, right-click the jar package and choose ---> Build Path ---> Add To Build Path. If you are creating a web project, put it in the lib folder under the WEB-INF.

2. JDBC Introduction

JDBC is a technology developed by Sun that can connect to databases using Java.

2.1 Basic JDBC knowledge

JDBC (Java Data Base 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 limited by database vendors: JDBC has two interfaces. One is for the application layer, which enables developers to call databases and process results through SQL, the provider of the database does not need to be considered. The other is the driver layer, which processes the interaction with the 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 makes it unnecessary 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 to multiple databases, as long as the corresponding driver is loaded;
Good versatility: The JDBC-ODBC bridge drive replaces JDBC functions 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 must be different, which makes it very difficult to change the data source.

2.2 process and principle of JDBC database connection

1) load the driver of the specified database in the development environment. For example, in the following experiment, the database is MySQL, so you need to download the JDBC-supported MySQL driver, and the development environment is MyEclipse, load the downloaded driver into the development environment (the detailed example shows how to load the driver ).

2) load the driver in the Java program. In Java programs, you can use "Class. forName ("specify the database driver") to load the driver added to the development environment. For example, the Code for loading the MySQL DATA driver is: Class. forName ("com. mysql. jdbc. driver ")

3) create a data Connection object: Create a database Connection object Connection through the DriverManager class. The DriverManager class acts between the Java program and the JDBC driver to check whether the loaded driver can establish a connection. Then, based on the URL, user name, and password of the database, it uses the getConnection method, create a JDBC Connection object. For example, Connection connection = DriverManager. getConnection ("URL for database Connection", "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. The connection code for creating a MySQL database is as follows:

Copy codeThe Code is as follows: Connection connectMySQL = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/myuser", "root", "root ");

4) create a Statement object: the object of the Statement class is mainly used to execute static SQL statements and return the results it generates. You can use the createStatement () method of the Connection object to create a Statement object. For example, Statement statament = connection. createStatement (); the Code for creating a Statement object is as follows:

Copy codeThe Code is as follows: Statement statamentMySQL = connectMySQL. createStatement ();
In addition, you can use PreparedStatement to code Statement. The PreparedStatement can prevent SQL injection attacks, prevent database buffer pool overflow, and ensure code readability and maintainability. The code for creating PreparedStatement is as follows:

Copy codeThe Code is as follows: String SQL = "Select title, year_made from movies where year_made >=? And year_made <=? ";
PreparedStatement ps = connectMySQL. prepareStatement (SQL );

5) Call the related methods of the Statement object to execute the corresponding SQL Statement: Use the execuUpdate () method for data updates, including insert and delete operations, for example, insert a piece of data code into the staff table:

Copy codeThe Code is as follows: statement. excuteUpdate ("insert into staff (name, age, sex, address, depart, worklen, wage)" + "VALUES ('tom1', 321, 'M', 'China ', 'personnel ', '3', '123 ')");

If PreparedStatement is used:

Prest. setInt (1st); // indicates that the first parameter is 1980prest. setInt (); ResultSet rs = prest.exe cuteQuery ();

You can call the executeQuery () method of the Statement object to query data. The result of the query gets the ResultSet object. The ResultSet indicates the set of data returned after the database is queried, the ResultSet 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:

Copy codeThe Code is as follows: ResultSet resultSet = statement.exe cuteQuery ("select * from staff ");

6) close database Connection: When the database is used up or you do not need to access the database, close the data Connection in time using the close () method of Connection.

3. Test code

After the environment is configured, you can write code to test whether the connection can be established!

Import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. statement; public class TestMysqlConn {public static void main (String [] args) {Connection con; Statement stmt; ResultSet rs; try {Class. forName ("com. mysql. jdbc. driver "). newInstance (); // test indicates the Database Name and _ test indicates the table name. The _ test table has three fields: id name description con = DriverManager. getConnection ("jdbc: mysql: // FIG: 3306/test", "root", "root"); stmt = con. createStatement (); rs = stmt.exe cuteQuery ("select * from _ test"); while (rs. next () {int num = rs. getInt ("id"); String name = rs. getString ("name"); String des = rs. getString ("description"); System. out. println (num + "" + name + "" + des);} stmt. close (); conn. close ();} catch (Exception e) {e. printStackTrace (); System. out. println ("connection failed ");}}}

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.