Java-database connection Java-MySQL connection ⑤: Connect to the database (MySQL) to search table information and display search results / September 2017

Connect to MySQL

Connect to the database (MySQL) and search for table information. I will also display the search results.

First, look at the following definition document and create a database and table from the command prompt.

Database name: testdb Table name: test_table table definition :

Next, let's register the data in test_table using the INSERT statement.

When using a text editor (Notepad) and a command prompt

At the command prompt, enter notepad Con2Mysql.java to open Notepad

When using Eclipse

Right-click on a blank area in Package Explorer-> New-> Select Java Project The New Java Project screen is displayed

Project name: Con2Mysql will do

Execution environment Using JRE: JavaSE-1.8. Press the "Next" button Press the "Add External JAR" button

• Addition of external JAR needs to be set for each project !!!

Open C: \ Program Files \ Java \ jdk x.x.x_xxx \ lib

• X depends on the installed JDK version

Select mysql-connector-java-x.x.xx-bin.jar and press the "Open" button Press the "Finish" button

Open the Java project folder () Right click on the src folder New-> Select Class The New Java Class screen is displayed

Name: Con2Mysql □ Check "public static void main (String [] args)" □ Check "Generate comment" Press the "Finish" button

Describe based on the following.

import java.sql.*;
public class Con2Mysql {
   public static void main(String[] args) {
     String msg = "";
     try {
     //Driver load
     Class.forName("com.mysql.jdbc.Driver");
     
     //Connect to MySQL
     Connection con = DriverManager.getConnection("jdbc:mysql://localhost/testdb", "root", "mysql");

     //Statement generation
     Statement stmt = con.createStatement();

     //Execute SQL
     String sqlStr = "SELECT * FROM test_table";
     ResultSet rs = stmt.executeQuery(sqlStr);

     //Loop result line
     while(rs.next()){
       //Record value
       int id = rs.getInt("user_id");
       String name = rs.getString("user_name");

       //display
       System.out.println(id + ":" + name);
       }

     //Close connection
     rs.close();
     stmt.close(); 
     con.close();
   }catch (ClassNotFoundException e){
     msg = "Failed to load the driver"; 
     System.out.println(msg);
   }catch (Exception e){
     msg = "Failed to load the driver";
     System.out.println(msg);
   } 
  }
}

After programming, let's run it.

If it is executed normally, the following display will be displayed.

1:taro 2:jiro 3:hanako

If it fails, an error will be displayed. Check if the database and tables are created correctly? Is the CLASSPATH and build bus set?

The format of the connection character string set in the driver manager is the following description rule.

"jdbc:mysql://HOSTNAME/DBNAME", "ID", "PASS"
Example: "jdbc:mysql://localhost/testdb", "user1", "pass4user1"

That's it.

We will edit it from time to time, so please feel free to let us know if you have any questions.

Java-database connection / Java-MySQL connection ① Overview of JDBC and JDBC driver (2) JDBC driver acquisition method (for MySQL) and data connection preparation ③-1 How to set CLASSPATH in environment variable ③-2 How to set CLASSPATH to Eclipse build bus ④ Load JDBC driver ⑤ Connect to database (MySQL) to search table information and display search results