java JDBC connecting mysql database

Preface

I have to admit the complexity of java code.

concept

1 Parameters for creating database links

    String driver;
    String url;
    String user;
    String password;

2 load database

class.formName(driver)

3 get database link

Connection conn =
    DriverManager
    .getConnection(url,user,password);

4. Write sql statement
Creating objects to execute sql statements

PreparedStatement stmt =
    conn.prepareStatement(sql);

5 execute sql statement

  • Modify operation
int row = stmt.executeUpdate();
  • Perform query operation
ResultSet rst = stmt.executeQuery();

6 treatment results

  • Modify operation, prompt execution result
  • Query operation, variable result set get data
while(rs.next){
    rs.getString("Column names");
}

code implementation

Code instance:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        insert();
        update();
    }

    //Insert operation
    public static void insert(){
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/qiandao";
        String user = "root";
        String password = "root";
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DriverManager.getConnection(url,user,password);
            if(conn!=null)
            {
                System.out.println("Connection successful!");
            }
            String sql = "insert into t_sign_info"
                    + "(stu_id,sign_date,sign_in,sign_out,sign_status)"
                    +" values(?,curdate(),curdate(),null,0)";
            stmt = 
                    conn.prepareStatement(sql);
            stmt.setObject(1, 2);
            int row = stmt.executeUpdate();
            if(row>0){
                System.out.println("Sign in succeeded");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
    //Modify operation

    public static void update(){
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/qiandao?UseUncode=true&charsacterEncoding=utf-8";//Process code
        String user = "root";
        String password = "root";
        try {
            Class.forName(driver);//Reflection can access private properties of a class
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DriverManager
                    .getConnection(url,user,password);
            String sql = "update t_stu_info"
                    + " set stu_name = ?,stu_pwd = ?"
                    + " where stu_id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setObject(1, "Wang Wu");
            stmt.setObject(2, "999999");
            stmt.setObject(3, 1);
            int row = stmt.executeUpdate();
            System.out.println(row);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }   
    }
}

Encapsulating jdbc

//Encapsulating jdbc operations
    public static int executeUpdate(String sql,Object...objs){
        int row = 0;
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/qiandao?UseUncode=true&charsacterEncoding=utf-8";
        String user = "root";
        String password = "root";
        try {
            Class.forName(driver);//Reflection can access private properties of a class
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DriverManager
                    .getConnection(url,user,password);
            stmt = conn.prepareStatement(sql);
            if(objs != null){//What is in the sql statement?
                for (int i = 0; i < objs.length; i++) {
                    stmt.setObject(i+1, objs[i]);
                }
            }
            row = stmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return row;
    }

Method call

String sql = "insert into t_stu_info"
                +" (stu_no,stu_name,stu_pwd)"
                +" value(?,?,?)";//Building sql statements
        int n = executeUpdate(sql, "S1003","Zhao Liu","123456");//Method call
        if(n>0){
            System.out.println("Success");
        }else{
            System.out.println("fail");
        }

Tags: SQL JDBC MySQL Java

Posted on Sat, 02 May 2020 23:15:13 -0700 by tskweb