[java operation mysql] java connects mysql database and queries data

It's inevitable to deal with the database in java development, so here's an article about connecting mysql with jdbc,
The main contents include:

  • 1. java connect mysql
  • 2. java queries mysql data.
  • 3. java inserts mysql data.
  • 4. java updates mysql data.
  • 5. java deletes mysql data.
  • 6. java executes mysql stored procedures.

ide: IntelliJ IDEA2017

It is necessary to import mysql-connector-java-5.1.45-bin.jar package.
maven load driver

 <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.45</version>
    </dependency>

After adding maven dependency, compile build, and the driver will be loaded automatically.

Here is the java operation mysql Code:

package DataStructureAndAlgorithm;

/**
 * Created by Administrator on 2017/12/24.
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class test6JavaJdbc {
    public static void main(String[] args) {
        Connection con;
        String driver="com.mysql.jdbc.Driver";
        //My database here is cgjr
        String url="jdbc:mysql://localhost:3306/cgjr?useUnicode=true&characterEncoding=utf-8&useSSL=false";
        String user="root";
        String password="12345";
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, user, password);
            if (!con.isClosed()) {
                System.out.println("Database connection successful");
            }
            Statement statement = con.createStatement();

//            Execute query statement
            String sql = "select * from persons;";//My form is called persons
            ResultSet resultSet = statement.executeQuery(sql);


//            Print what you find
            String name;
            String num;
            while (resultSet.next()) {
                name = resultSet.getString("name");
                num = resultSet.getString("num");
                System.out.println(name+'\t'+num);
            }


            //            Execute insert statement
            String sql2="INSERT INTO `persons` (`name`, `num`) VALUES ('poet and essayist', '45');";
            statement.executeUpdate(sql2);

//                       Execute UPDATE statement
            String sql3="UPDATE persons set num=66 WHERE `name`=\"poet and essayist\"";
            statement.executeUpdate(sql3);



//               Execute delete statement
            String sql4="delete from persons WHERE `name`=\"poet and essayist\"";
            statement.executeUpdate(sql4);

//            Execute call stored procedure

            String sql5="call add_student(3)";
            statement.executeUpdate(sql5);


//            Close connection
            resultSet.close();
            con.close();
            System.out.println("Database closed connection");
        } catch (ClassNotFoundException e) {
            System.out.println("Database driver not installed");

        } catch (SQLException e) {
            System.out.println("Database connection failed");
        }
    }
}

Operation result:

Database connection successful
zhangsan    20
laidefa 24
 Zhang 3 57
 Xu Zhimo 66
 Database closed connection

Process finished with exit code 0

Tags: Java MySQL Database SQL

Posted on Thu, 30 Apr 2020 19:11:38 -0700 by Gugel