MyEclipse Connection Mysql Database sample code _mysql

1. Environment configuration

Download Address:http://www.mysql.com/downloads/mysql/ really trouble, download words also need to register and login and fill in a table. The information above is quite full, the information is also acceptable to fill in the random ~ ~ Download after the prompt installation can be installed, and finally set the password to login MySQL. After the installation is complete, test the connection database. In the Start-program click MYSQL5.5 Command Line cilent, enter the password just set, you should be able to connect to the MySQL server.

In the installed package, Mysql_server\connector J xxxx can find a jar package that is required for Java programs to connect to the MySQL database. Without this package, the program code will prompt: ClassNotFoundExceptioncom.mysql.jdbc.Driver error.

Create a new Java project and create a new folder in the project that holds the jar package (such as Lib), copy Mysql-connector-java-x.x.x-bin.jar to the folder, select the Jar package right-click--->build Path--- >add to build Path, you can. If a new Web project is created, it is placed in the Lib folder under Web-inf.

2. JDBC Introduction

JDBC is a technology that sun companies can use to connect to databases in the Java language.

2.1 JDBC Basics

JDBC (Java Data Base Connectivity,java database connection) is a Java API for executing SQL statements that provides unified access to a variety of relational databases, consisting of a set of classes and interfaces written in the Java language. JDBC provides database developers with a standard API for building more advanced tools and interfaces that enable database developers to write database applications with a pure Java API and to run across platforms without being limited by database vendors.

1, cross-platform operation: This is the inheritance of the Java language "once compiled, running everywhere" characteristics;

2, not limited by the database vendor: The ingenious is that JDBC has two kinds of interfaces, one is the application layer, the role is to enable developers to call the database and processing results through SQL, without the need to consider the database provider; the other is the driver layer, which handles interaction with specific drivers, The JDBC driver can use the JDBC API to create a bridge between Java programs and data sources. The application needs to be written once and can be moved to a variety of drivers. Sun provides a driver manager, database vendors-such as MySQL, Oracle, the driver provided to meet the requirements of the driver can be recognized, you can work properly. Therefore, JDBC is not restricted by the database vendor.

The JDBC API can be used as a link between a Java application and a variety of relational databases, bringing convenience and a negative impact, following are the advantages and disadvantages of JDBC.

The advantages are as follows:

easy to operate : JDBC makes it unnecessary for developers to use complex drives to invoke commands and functions;
Strong portability : JDBC supports different relational databases, so you can enable the same application to support access to multiple databases, just by loading the appropriate drivers;
good commonality : Jdbc-odbc bridge drives convert JDBC functions to ODBC;
Object-oriented : You can encapsulate a common JDBC database connection into a class and call it directly when you use it.

Disadvantages are as follows:

The speed of accessing data records is affected to some extent;
Changing data source difficulties: JDBC can support a variety of databases, the operations between the various databases must be different, which brings a lot of trouble to change the data source

The process and principle of 2.2 JDBC Connection database

1 load the driver for the specified database in the development environment. For example, in the next experiment, the database used was MySQL, so it was necessary to download the MySQL-enabled driver, and the development environment was myeclipse, loading the downloaded driver into the development environment (the example would explain how to load it).

2 load the driver in the Java program. In Java programs, you can load drivers added to the development environment by using the Class.forName ("Specify the driver for the database"), such as the code for the data driver that loads MySQL: 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 works between the Java program and the JDBC driver to check whether the loaded driver can establish a connection, and then, through its Getconnection method, creates a JDBC based on the database's URL, user name, and password Connection object. such as: Connection Connection = drivermanager.getconnection ("URL of the connection database", "username", "password"). Where the Url= protocol name +IP address (domain name) + port + database name; user name and password refers to the username and password used when you log in to the database. A specific example creates MySQL's database connection code as follows:

Copy Code code as follows:

Connection connectmysql = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/myuser", "root", "root");

4 Create the Statement object: The Statement class is primarily an object that executes a static SQL statement and returns the results it generates. You can create a statement object by using the Createstatement () method of the Connection object. For example: Statement statament = Connection.createstatement (); The specific example creates the statement object code as follows:

Copy Code code as follows:

Statement statamentmysql =connectmysql.createstatement ();

In addition, generally can use PreparedStatement to code statement, Factor PreparedStatement can prevent SQL injection attack, prevent database buffer pool overflow, code readability, maintainability. The specific example creates the PreparedStatement code as follows:

Copy Code code as follows:

String sql = "Select title, Year_made from movies where Year_made >=?" and Year_made <=? ";
PreparedStatement PS =connectmysql.preparestatement (SQL);

5 Call the statement object's related methods to execute the corresponding SQL statement: the Execuupdate () method is used to update the data, including inserts and deletes, such as the code to insert a piece of data into the staff table:

Copy Code code as follows:

Statement.excuteupdate ("INSERT into Staff" (name, age, Sex,address, Depart, Worklen,wage) "+" VALUES (' Tom1 ', 321, ' M ', ' C Hina ', ' personnel ', ' 3 ', ' 3000 ');

If PreparedStatement is used, then:

Prest.setint (1,1980); Indicates that the 1th parameter is 1980
prest.setint (2,2004);
ResultSet rs = Prest.executequery ();

Querying the data by calling the ExecuteQuery () method of the Statement object, and the query result gets the ResultSet object, resultset represents the collection of data returned after the query database is executed, The ResultSet object has a pointer that can point to the current data row. With the next () method of the object, make the pointer point to the next line, and then remove the data with the column number or field name. If the next () method returns NULL, there is no data in the next row. Use the sample code as follows:

Copy Code code as follows:

ResultSet ResultSet = Statement.executequery ("SELECT * from staff");

6 Close the database connection: When you are finished using the database or do not need to access the database, close the data connection in a timely manner through the connection closing () method.

3. Test code

After configuring the environment, you can write code to test whether it can connect!

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 is the database name and _test is the table name. There are three fields in the _test table: id Name description
   con = drivermanager.getconnection ("Jdbc:mysql://127.0.0.1:3306/test", "root", "Root");
 
   stmt = Con.createstatement (); 
 
   rs = Stmt.executequery ("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 the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.