JAVA access to MySQL database (using methods and tests)

Our Java class recently talked about the database, and the teacher has always stressed the importance of setting up a database environment (JDBC), just before my computer was re-installed, and I could take this opportunity to review the JDBC build.

Description: The JDBC framework used in this article is based on the Java SE 1.8 & MySQL & Eclipse

Tool Download:

MySQL V5.5 (x64) 64-bit
MySQL V5.5 (x86) 32-bit
Choose the appropriate MySQL version according to your computer's situation. My computer is 64-bit, so this article is using 64-bit explanation, if there are students in the use of the 32-bit version of the problem can be contacted at any time.

JDBC Driver (MySQL)

MYSQL Visualization Tool Front

I. Installation configuration MySQL

1. First Open run Mysql-5.5.29-winx64.msi (that is, MySQL V5.5 above), you will go to the installation interface:

2. Next ... Next->next ... Always click Next when you see this interface:

This is the installation directory where MySQL is selected. My habit is that development tools generally choose the default path . Of course you might want to use a different path, but keep in mind that the path does not contain Chinese .
Continue next.

This is the port number of the MySQL service, we'd better not change it, but remember this value 3306.
Then next ... Welcome to the Pit! (I didn't let you step on it, no hurry)

This interface is the character set of our MySQL system. The default is to let us choose the character set "Latin1", I asked you do you know what character set this is ... Then you don't blame me ignorant, I was the first direct default past, finally found that my database can not use Chinese! will appear garbled. Then it was a long time to solve.

Why don't we solve the problem in the first place, right? Here we choose the third item, and then choose the character set "GBK", this does not have to explain it ... See:

Continue next. See the Setup Administrator account information interface:

Check the first, and then enter the password (recommended for students with bad memory password "123456" ... = =). Once you've set your password, you'll be logged into MySQL's account:
Username:root
password:*** (Your password) *

Next ... etc... Wait ...

But trust me, this process won't last long. Done:

3. Next, check if MySQL is installed correctly:
"My Computer", "Manage", "services". To see if the MySQL service is started:

If you find that your MySQL has not yet started, you can start the service in "run" using "net start MySQL". Then check the running status of MySQL in Task Manager:

Two. Using the MySQL Database

We've successfully installed the MySQL database, so how about a warm-up next?
The first step to use MySQL database is to authenticate, go to the command line interface, enter the following instructions (if you make the installation using the default installation path, usually this directory, if you use a custom directory to enter the corresponding directory is good), Go to the Bin folder in the MySQL installation directory (the development tool that holds MySQL):

cd C:\Program Files\MySQL\MySQL Server 5.5\bin

Next, you'll be authenticated with the following directives:

-u-p你的密码

If the login is successful, you will see the following interface:

OK, now it's time to use MySQL database.
Next, do the following:

1. Create a database TESTJDBC: CREATE DATABASE testjdbc;2. Using the TESTJDBC database: Use Testjdbc;3. Create a UserInfo data table (containing attribute username (char (10)), Password (varchar (15)): CREATE TABLE UserInfo (userName char), password varchar(15 ));4. Insert Data: Insert  into UserInfo VALUES(' Little Hyun ', ' 111111 ');Insert  into UserInfo VALUES(' Xiao Ming Jie jj ', ' 222222 ');INSERT  into UserInfo VALUES(' small Airways ', ' 333333 ');

I wonder if you notice that our SQL statements are all ";" At the end, this is obviously not SQL syntax, we need to take ";" after each statement when operating MySQL under DOS.

The operation process is as follows (front high energy!!! )

CREATE DATABASE TestJDBC;

USETestJDBC;

CREATE TABLE UserInfo(userName char(10), password varchar(15));

DESC UserInfo;  (查看表结构)

INSERT INTO UserInfo VALUES(‘小炫‘, ‘111111‘)；INSERT INTO UserInfo VALUES(‘小铭杰JJ‘, ‘222222‘)；INSERT INTO UserInfo VALUES(‘小航‘, ‘333333‘)；

Now we have created a TESTJDBC database with a data table containing three data:

SELECTFROM UserInfo

Three. Using Java to connect and manipulate the database

The preparations are all done, and the next step is to get to work. Back to our old line: Use Java to manipulate the database.
First, we look at JDBC in a single graph:

A Java database connection, (Java db Connectivity, or JDBC) is an application interface in the Java language that regulates how a client program accesses a database, providing methods such as querying and updating data in a database. JDBC is a relational-oriented database. Simply put, this is the Java API used to execute SQL statements, which allows us to manipulate the relational database directly using Java programming. Encapsulation allows developers to complete SQL execution using a pure Java API.

We are now under control, so far we have done the work of the bottom of the database has been set up, in order to use the database in Java programs, but also need:
* JDBC Driver
* JDBC API
* JAVA Program (this is nonsense-.-| | |)

To get started, we'll start by creating a Java project under Eclipse (this ... No, it's not necessary. )

JDBC Drive

is the JDBC driver (MySQL) We downloaded earlier: Mysql-connector-java-5.1.7-bin.jar.
In fact, this is a third-party library provided by the MySQL database to connect the MySQL database to Java, so we just have to import the library into our project.
1. Create a new user environment variable:

变量名:CLASSPATH变量值:;目录:mysql-connector-java-5.1.7-bin.jar

I put the jar file directly under the C drive, you can certainly put it in other places, but still that sentence, do not put in the path contains Chinese characters in the directory.

2. Import the jar into the Java project:
"Project name", "Build path"->configure build Path

"Add Library", check the jar file

After adding the success, you will see this jar in your project view:

JDBC API

About the JDBC API. The class teacher has made it very clear. I'll just review it here.
To use JDBC, there are several important classes to know:

//重要的类:java.sql.Connection;        //数据库连接实例java.sql.DriverManager;     //数据库驱动管理类，调用其静态方法getConnection并传入数据库的URL获得数据库连接实例java.sql.Statement;         //操作数据库要用到的类，主要用于执行SQL语句java.sql.ResultSet;         //数据库查询结果集

Java programs

The following is an example of MySQL, a simple explanation of the Java program operation of the database method.

The steps for using JDBC are as follows:
Description:
The Java project has been imported into the JDBC Drive (jar);
Considering the security of the database, I encapsulated the login information of my database and put it under the Values.java:*

Values.java:

publicclass Values{    publicstatic"com.mysql.jdbc.Driver";    //MySQL JDBC驱动字符串    publicstatic"jdbc:mysql://localhost:3306/数据库名?"            "user=用户名&password=密码&useUnicode=true&characterEncoding=UTF8";//数据库Url,用来标识要连接的数据库，其中数据库名、用户名、密码是根据你自己的数据库情况设定}

1. Loading the JDBC driver in a Java program

Class.forName(Values.DRIVER_MYSQL);

2. Create a database Connection object

Connection connection = DriverManager.getConnection(Values.URL);

3. Create a statement object

Statement statement = connection.createStatement();

4. Invoke the corresponding method of the statement object to manipulate the database

Here is the demo I used to test the data:
Testjdbc.java:

 Public  class testjdbc{    PrivateStatement Statement; Public Testjdbc() {Try{Class.forName (values.driver_mysql);//load JDBC driverSystem.out.println ("Driver Load Success."); Connection Connection = drivermanager.getconnection (Values.url);//CREATE database Connection objectstatement = Connection.createstatement ();//Create statement Object}Catch(Exception e) {//TODO auto-generated catch blockE.printstacktrace (); }    }/ * * Returns a result set based on SQL query database * INPUT: SQL statement * return value: ResultSet query result */     PublicResultSetQuery(String SQL) {ResultSet result =NULL;Try{result = Statement.executequery (SQL); }Catch(SQLException e) {//TODO auto-generated catch blockE.printstacktrace (); }returnResult }/* * Print data for userinfo table * Input: Result set (data table) * Return value: Empty */     Public void Printuserinfo(ResultSet result) {Try{ while(Result.next ()) {System.out.println ("Usernname:"+ result.getstring (1)                         +", Password:"+ result.getstring (2)); }        }Catch(SQLException e) {//TODO auto-generated catch blockE.printstacktrace (); }    }/ * * Perform data operation * INPUT: SQL statement * return value: Empty */     Public void ExecuteSQL(String SQL) {Try{statement.execute (SQL); }Catch(SQLException e) {//TODO auto-generated catch blockE.printstacktrace (); }    } Public Static void Main(string[] args) {//TODO auto-generated method stubString sql ="SELECT * from UserInfo"; TESTJDBC db =NewTestjdbc ();        ResultSet result = db.query (SQL);    Db.printuserinfo (result); }}

Look at what we did above, first we put the preparation of the operation JDBC in the construction method, once we create the Testjdbc object, we get the database connection and a corresponding statement object. Then there are three methods defined in the class:
* ResultSet query (String SQL)
* void ExecuteSQL (String sql)
* void Printuserinfo (ResultSet result)

These methods are encapsulated with the JDBC API used to manipulate the database, the specific functions have been commented on the code. In the main function, we first create a Testjdbc object, then execute the query statement, get all the data of the UserInfo table and print it out. Run the program and get the following result:

Now let's add the code that inserts the data into the main function:

publicstaticvoidmain(String[] args)    {        // TODO Auto-generated method stub        "SELECT * FROM UserInfo";        new TestJDBC();        db.executeSql("INSERT INTO UserInfo VALUES(‘小明‘, ‘666666‘)");   //插入一条数据        ResultSet result = db.query(sql);        db.printUserInfo(result);    }

Run the program and get the following result:

Description we inserted data successfully!

Four. mysql Visualizer Mysql-front: Using the graphical interface to operate MySQL

We have always been in the command to operate MySQL, for the single insert Delete query data is acceptable, but if we want to use the instructions to write SQL program t ... Oh, my God! Debugging is going to be dead. So we still have to master the use of Mysql-front, I am sure you will be more inclined to graphics operations.
The first is to install Mysql-front, this is more simple, direct next, next ...

After the installation of the run Mysql-front, to do some configuration, here is not easy to change, these configurations to be based on our MySQL database configuration, if we do not define the host and port number, you just set it up:

Database, just choose the database we built earlier, of course, you can also create a new database.

Open and go to our MySQL database. Open "TESTJDBC" we can see the database we created earlier,

View the structure of a data table

View data Table Current data

Now let's execute an INSERT statement:

INSERT INTO UserInfo VALUES(‘小B‘, ‘555555‘)

Click Run, and then look at the data table data:

The new data has been inserted into the data table (you may not see the new data at once, so we just need to refresh it.) What the! I haven't seen it yet! There must be something wrong with your SQL statement. )

Finally we go back to the previous Java Demo, run the program, the results are as follows:

Oh, my God! The JDBC build and test has been completed unconsciously!
Yeah! is not very nice!

Demo:jdbcformysqldemo

JAVA access to MySQL database (using methods and tests)