Basic usage of JDBC

JDBC fixing steps:

Load driver

String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT";
String username="root";
String password="123456";
Class.forName("com.mysql.cj.jdbc.Driver");//I don't know why com.mysql.jdbc.Driver will report an error. Please leave a message if you have any known boss

Connect to database, on behalf of database

Connection connection = DriverManager.getConnection(url, username, password);

Send SQL object statement to database: CRUD

Statement statement = connection.createStatement();

Write SQL (different SQL according to business)

String str1="select * from users";
String str2="insert into users values (4,'Zhao Liu','145667','werwef.@eq',current_time) ,(5,'pseudo-ginseng','53234','fsd@df',current_time)";
String str3="delete from users where id=5";
String str4="update users set password='987654' where id=4";

Execute SQL

//        int i = statement.executeUpdate(str2);
//        int i = statement.executeUpdate(str3);
//        int i = statement.executeUpdate(str4);
        ResultSet resultSet = statement.executeQuery(str1);

Traversal result set

while (resultSet.next()){
            System.out.println("id:"+resultSet.getInt("id"));
            System.out.println("name:"+resultSet.getString("name"));
            System.out.println("password:"+resultSet.getString("password"));
            System.out.println("email:"+resultSet.getString("email"));
            System.out.println("birthday:"+resultSet.getString("birthday"));
        }

Close connection

resultSet.close();
statement.close();
connection.close();

Add:

  • statement.executeQuery(); / / query

  • statement.executeUpdate(); / / add, delete or modify

  • resultset. beforeFirst(); / / move to the front

  • resu1tSet. afterlast(); / / move to the back

  • resultset.next(); / / move to next data

  • resultset. previous(); / / move to the previous line

  • resu1tset. absolute(row); / / move to the specified row

  • statement is not safe to use prepareStatement to prevent SQL injection

    Here is how to use prepareStatement

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT";
    String username="root";
    String password="123456";
    //Load driver
    Class.forName("com.mysql.cj.jdbc.Driver");
    //Connect to database
    Connection connection = DriverManager.getConnection(url, username, password);
    //Write SQL
    String str5="insert into users (id,name,password,email,birthday)values (?,?,?,?,?)";
    //precompile
    PreparedStatement ps = connection.prepareStatement(str5);

    ps.setInt(1,6);				//Give the first placeholder? Assignment 6
    ps.setString(2,"Hu Ba");	   //Give the second placeholder? Assign "Hu Ba"
    ps.setString(3,"1223235");	//To the third placeholder? Assignment "1223235“
    ps.setString(4,"ew@12");	//To the fourth placeholder? Assignment“ ew@12 "
    ps.setDate(5,new Date(new java.util.Date().getTime()));
    							//Give the fifth placeholder? Assignment 2020-05-19
    
    //implement
    int i = ps.executeUpdate();
    if (i>0){
        System.out.println("Insert successful");
    }
	//Close connection
    ps.close();
    connection.close();
}

Keywords: MySQL JDBC SQL Database

Added by PHPfolife on Wed, 20 May 2020 17:31:54 +0300