This article introduces how Java can connect to MySQL database using JDBC.

Summary of JDBC

JDBC (Java Data Base Connectivity) is a Java API for executing SQL statements. It provides unified access to multiple relational databases. JDBC consists of a set of classes and interfaces written in the Java language. Is a standard specification for Java to access databases.

JDBC provides a benchmark against which more advanced tools and interfaces can be built to enable database developers to write database applications.

JDBC requires a connection driver. The two devices must communicate with each other in a data format specified by the device provider. The device provider provides driver software for the device to communicate with the device.

Mysql-connector-java-5.1.39-bin. Jar Download Now

JDBC theory

The Java specification that provides access to the database is called JDBC, and the implementation class that the manufacturer provides the specification is called drivers.



JDBC is the interface, the driver is the implementation of the interface, without the driver will not be able to complete the database connection, thus can not operate the database! Each database vendor needs to provide its own driver to connect to its own database, which means that drivers are generally provided by the database generator vendor.

JDBC Development Steps

Java database links mainly include the following steps:

  1. Registration drive
  2. Obtain a connection
  3. Get the statement execution platform
  4. Execute SQL statement
  5. The processing results
  6. Release resources

Importing the driver package (JAR package)

Create a lib directory under the project folder to store all the JAR packages required for the current project. Right-click the JAR package and run build Path/Add to Build Path

Registration drive

Driver. The MySql Driver package provides the implementation classes com.mysql.jdbc.driver and DriverManager tools. RegisterDriver () is used to register the Driver. The parameter to the java.sql.Driver method is java.sql.Driver, so we can register it with the following statement:

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

Copy the code

However, this method is not recommended for the following reasons:

/ *

By querying the source code of com.mysql.jdbc.driver, we find that the Driver class "actively" registers itself

* /

public class Driver extends NonRegisteringDriver implements java.sql.Driver {

    static {

        try {

           java.sql.DriverManager.registerDriver(new Driver());

        } catch (SQLException E) {

throw new RuntimeException("Can't register driver!" );

       }

   }

...

}



// This will cause the driver to be registered twice and the code is not easy to extend and maintain

Copy the code

Typically in development we use class.forname () to load a driver Class that uses a string description. If you load a Class into memory using class.forname (), the static code for that Class is executed automatically. So we can use the following code to register the driver:

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

Copy the code

Obtain a connection

Need method of connection for DriverManager. GetConnection (url, username, password), three parameters respectively, the location of the need to connect to the database url (url) username user name password, “password.

Url is more complex, the following is the mysql url: JDBC: mysql: / / localhost: 3306 / mydb JDBC url specified format consists of three parts, each part using colon delimited in the middle. The first part is JDBC, which is fixed; Mysql > connect to mysql; mysql > connect to mysql; The third part is specified by the DATABASE vendor. We need to understand the requirements of each DATABASE vendor. The third part of mysql consists of the IP address of the DATABASE server (localhost), port number (3306), and DATABASE name (myDB).

Code: Connection con = DriverManager. GetConnection

(" JDBC: mysql: / / localhost: 3306 / mydb ", "root", "root");

Copy the code

Get the statement execution platform

There are three common methods:

  • int executeUpdate(String sql); – Execute the INSERT update delete statement.
  • ResultSet executeQuery(String sql); – Execute the SELECT statement.
  • boolean execute(String sql); – True for select statements false for other statements.
String SQL = "SQL statement ";

Statement STMT = con.createstatement ();

Copy the code

The result processing

We use a ResultSet to process the result, which is actually a two-dimensional table. We can call its Boolean Next () method to point to a row. When next() is called for the first time, In this case, the getXXX(int col) method provided by the ResultSet can be used to retrieve the specified column data (instead of the index starting at 0, columns starting at 1) :

rs.next(); // point to the first line

rs.getInt(1); // Get the first row, first column

Copy the code

The common methods are as follows:

  • Object getObject(int index)/Object getObject(String name) Gets any Object
  • String getString(int index)/Object getObject(String name) Obtains a String
  • Int getInt(int index)/Object getObject(String name) Gets an integer
  • Double getDouble(int index)/Object getObject(String name

Release resources

As with IO streams, everything needs to be turned off after use! The order of closure is first obtained, then closed, and then obtained, first closed.

rs.close();

stmt.close();

con.close();

Copy the code

SQL Injection Problems

Suppose there is a login case SQL statement like this:

SELECT * FROM user table WHERE NAME = username AND PASSWORD = PASSWORD;

Copy the code

In this case, after the user enters the correct account and password, the user can log in if the information is found. However, when the user enters the account XXX and password XXX ‘OR’ a ‘=’ a ‘, the actual code is changed to:

SELECT * FROM user WHERE NAME = 'XXX' AND PASSWORD = 'XXX' OR 'a' = 'a';

Copy the code

At this point, the above query statement can always query the result. The user is logged in successfully, which we obviously don’t want, and that’s the PROBLEM with SQL injection. To do this, we use a PreparedStatement to solve the corresponding problem.

Preprocessing object

When preparing objects in PreparedStatement, it is recommended that all the actual parameters of each SQL statement be separated by commas.

String sql = "insert into sort(sid,sname) values(? ,?) ";

PreparedStatement Preprocesses the object code:

PreparedStatement psmt = conn.prepareStatement(sql)

Copy the code

Common methods:

Execute SQL statement int executeUpdate(); – Execute the INSERT update delete statement. ResultSet executeQuery(); – Execute the SELECT statement. Boolean execute(); – True for select statements false for other statements.


Set the actual parameter void setXxx(int index,Xxxxx) Sets the specified parameter to the xx value of the given Java. When this value is sent to the database, the driver converts it to a value of type SQL Xxx.

Preprocess the executeUpdate method of the object

The execution of the insert\update\delete statement of the record is accomplished by preprocessing the executeUpdate method of the object. The operation format is as follows:

  1. Registration drive
  2. Get connected
  3. Getting preprocessed objects
  4. SQL statement placeholders set actual parameters
  5. Execute SQL statement
  6. Release resources

Insert operation

Inserts the specified new content into the table

public void demo01() throws Exception {

// 1 Registers the driver

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

// 2 Get the connection

        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");

// 3 Get preprocessed objects

String sql = "insert into sort(sname) values(?) ";

       PreparedStatement stat = conn.prepareStatement(sql);

// 4 SQL statement placeholder to set actual parameters

Stat. SetString (1, "luxury ");

// 5 Execute the SQL statement

     int line = stat.executeUpdate();

System.out.println(" add new records: "+ line);

// 6 Release resources

        stat.close();

       conn.close();

}

Copy the code

Update operation

public void demo02() throws Exception {

// 1 Registers the driver

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

// 2 Get the connection

        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");

// 3 Get preprocessed objects

String sql = "update sort set sname=? where sid=?" ;

     PreparedStatement stat = conn.prepareStatement(sql);

// 4 SQL statement placeholder to set actual parameters

Stat. SetString (1, "Digital products ");

      stat.setInt(2, 1);

// 5 Execute the SQL statement

       int line = stat.executeUpdate();

System.out.println(" update number: "+ line);

// 6 Release resources

        stat.close();

       conn.close();

}

Copy the code

Delete operation

public void demo03() throws Exception {

// 1 Registers the driver

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

// 2 Get the connection

        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");

// 3 Get preprocessed objects

String sql = "delete from sort where sid=?" ;

        PreparedStatement stat = conn.prepareStatement(sql);

// 4 SQL statement placeholder to set actual parameters

     stat.setInt(1, 1);

// 5 Execute the SQL statement

       int line = stat.executeUpdate();

System.out.println(" delete records: "+ line);

// 6 Release resources

        stat.close();

       conn.close();

}

Copy the code

Preprocess the executeQuery method of the object

The record’s SELECT statement is executed by preprocessing the object’s executeQuery method. The operation format is as follows:

  1. Registration drive
  2. Get connected
  3. Getting preprocessed objects
  4. SQL statement placeholders set actual parameters
  5. Execute SQL statement
  6. Working with the result set (traversing the result set)
  7. Release resources

Query operation

public void demo04() throws Exception {

// 1 Registers the driver

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

// 2 Get the connection

        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");

// 3 Get preprocessed objects

     String sql = "select * from sort";

      PreparedStatement stat = conn.prepareStatement(sql);

// 4 SQL statement placeholder to set actual parameters

// 5 Execute the SQL statement

     ResultSet rs = stat.executeQuery();

// 6 Process result set (traversal result set)

       while( rs.next() ){

// Get the category ID of the current row

String sid = rs.getString("sid"); // The method parameter is the column name in the database table

// Get the category name of the current row

            String sname = rs.getString("sname");

// Display data

          System.out.println(sid+"-----"+sname);

      }

// 7 Release resources

        rs.close();

     stat.close();

       conn.close();

}

Copy the code
public void demo05() throws Exception {

// 1 Registers the driver

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

// 2 Get the connection

        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");

// 3 Get preprocessed objects

String sql = "select * from sort where sname=?" ;

        PreparedStatement stat = conn.prepareStatement(sql);

// 4 SQL statement placeholder to set actual parameters

Stat. SetString (1, "luxury ");

// 5 Execute the SQL statement

        ResultSet rs = stat.executeQuery();

// 6 Process result set (traversal result set)

       while( rs.next() ){

// Get the category ID of the current row

String sid = rs.getString("sid"); // The method parameter is the column name in the database table

// Get the category name of the current row

            String sname = rs.getString("sname");

// Display data

          System.out.println(sid+"-----"+sname);

      }

// 7 Release resources

        rs.close();

     stat.close();

       conn.close();

}

Copy the code

JDBC tools

The “get database connection” operation, which will be present in all future add, delete, change, and search functions, can encapsulate the utility class JDBCUtils. Provides a way to get connection objects to achieve code reuse. The utility class provides the method: public static Connection getConn (). The code is as follows:

/ *

* JDBC utility classes

* /

public class JDBCUtils {

    public static final  String DRIVERNAME = "com.mysql.jdbc.Driver";

   public static final  String URL = "jdbc:mysql://localhost:3306/mydb";

   public static final  String USER = "root";

  public static final  String PASSWORD = "root";



  static {

        try {

           Class.forName(DRIVERNAME);

      } catch (ClassNotFoundException e) {

System.out.println(" Database driver registration failed!" );

       }

   }

// Provide a method to get the connection

 public static Connection getConn() throws Exception {

// 2. Get the connection

      Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);

// Return the connection

     return conn;

    }

}

Copy the code

WeChat pay

Alipay

  • Author: Gyl-coder
  • Links to this article: Gyl – coder. Top/JavaMysqlCo…
  • Copyright Notice: All articles on this blog are licensed under a CC BY-NC-SA 3.0 license unless otherwise stated. Reprint please indicate the source!