Getting started with JDBC

Detailed explanation

１.０DriverManager

Function 1

Function 2

2.0 connection object

3.0 statement object

4.0 ResultSet

A case of traversing result set

import java.sql.*;

public class DQLtest {
    public static void main(String[] args)  {
        //Registration driven
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet =null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //Define an SQL statement
            String sql="select * from student";
            //The URL of the database connection object should add "servertimezone = GMT% 2B8 & usessl = false" after the original database
            //Because the jar package I imported is the latest version, I want to add SSL connection status
            connection = DriverManager.getConnection("jdbc:mysql:///db2" +
                    "?serverTimezone=GMT%2B8&useSSL=false", "root", "root");
            //Objects that execute SQL
            statement = connection.createStatement();
            //Get result set object
            resultSet = statement.executeQuery(sql);
            while( resultSet.next ()) // the next method determines whether the cursor is on the last line. If yes, it returns false; otherwise, it returns true
            {
                int id = resultSet.getInt("ID");
                int age= resultSet.getInt("age");
                String name = resultSet.getString("name");
                String cls = resultSet.getString("class");
                System.out.println(id+" "+age+" "+name+" "+cls);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}

Print results:

1 18 lisa 191
2 19 JK 192
3 18 rose 191
4 19 V 192
5 20 suga 181
6 21 jimin 182

Creation and use of JDBC tool class

1.0 configuration file

drive=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///db2?serverTimezone=GMT%2B8&useSSL=false
user=root
password=root

Creation of 2.0 JDBC util

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class JDBCutil {
    /*
    *1. Register driver and database connection object URL user password in static code block
    *2. Release resources
    * */
    public static String drive;
    public static String url;
    public static String user;
    public static String password;
    static {
        Properties properties = new Properties();
        //Get the file mode in SRC Directory - > classloader
        //Get the bytecode file first
        ClassLoader classLoader = JDBCutil.class.getClassLoader();
        URL resource = classLoader.getResource("jdbc.properties");
        //URL uniform resource locator gets absolute path
        String path = resource.getPath();
        try {
            properties.load(new FileReader(path));
        } catch (IOException e) {
            e.printStackTrace();
        }
        drive = properties.getProperty("drive");
         url = properties.getProperty("url");
         user = properties.getProperty("user");
         password = properties.getProperty("password");

    }
    public static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(url,user,password);
    }

    public static void close(Connection conn, Statement stat)
    {
        try {
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            stat.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    //Overload
    public static void close(Connection conn, Statement stat, ResultSet res)
    {
        try {
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            stat.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            res.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

Optimize the case and use the JDBC util class

import java.sql.*;

public class DQLtest {
    public static void main(String[] args)  {
        //Registration driven
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet =null;
        try{
            //Class.forName("com.mysql.cj.jdbc.Driver");
            //Define an SQL statement
            String sql="select * from student";
 
           // connection = DriverManager.getConnection("jdbc:mysql:///db2" +
           //"?serverTimezone=GMT%2B8&useSSL=false", "root", "root");
           connection = JDBCutil.getConnection();

            //Objects that execute SQL
           statement= connection.createStatement();
            //Get result set object
           resultSet = statement.executeQuery(sql);
            while( resultSet.next ()) // the next method determines whether the cursor is on the last line. If yes, it returns false; otherwise, it returns true
            {
                int id = resultSet.getInt("ID");
                int age= resultSet.getInt("age");
                String name = resultSet.getString("name");
                String cls = resultSet.getString("class");
                System.out.println(id+" "+age+" "+name+" "+cls);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCutil.close(connection,statement,resultSet);
        }
    }
}

Page login cases

　PreparedStatement　

import java.sql.*;
import java.util.Scanner;

public class Usertest {
    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        System.out.println (please enter your name:);
        String username=input.next();
        System.out.println (please enter your password:);
        String password=input.next();
        Connection conn=null;
        PreparedStatement prep =null;
        ResultSet set=null;
        try{
            conn = JDBCutil.getConnection();
            //String sql="SELECT *FROM USER WHERE NAME='"+username+"'AND PASSWORD='"+password+"'";
            String sql="SELECT *FROM USER WHERE NAME=? AND PASSWORD=?";
            prep = conn.prepareStatement(sql);
            //For? assignment
            prep.setString(1,username);
            prep.setString(2,password);
            ResultSet resultSet = prep.executeQuery();
           if(resultSet.next())
           {
               System.out.println (login successful! ");
           }
           else {
               System.out.println "Login failed! ");
           }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCutil.close(conn,prep,set);
        }

    }
}

JDBC management transaction

Database connection pool

1.0 introduction

Focus on Druid

Let’s create a druid tool class to use（mysql-connector-java-8.0.11.jar）（druid-1.1.21.jar）

Define profile druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///db2?serverTimezone=GMT%2B8&useSSL=false
username=root
password=root
#Number of initial connections
initialSize=5
#Maximum connections
maxActive=10
#Maximum waiting time
maxWait=3000

Define a tool class

package utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class Druidutil {
    public static DataSource ds;
    //Import jar package
    //Define profile
    //Load configuration file
    static{

        try {
            Properties prop = new Properties();
            prop.load(Druidutil.class.getClassLoader().getResourceAsStream("druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //Get connection object
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    //Release resources

    public static void close(Statement stat,Connection conn)
    {
     close(null,stat,conn);
    }


    public static void close(ResultSet res,Statement stat, Connection conn)
    {
        if(res!=null)
        {
            try {
                res.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(stat!=null)
        {
            try {
                stat.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null)
        {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //Get connection pool
    public static DataSource getDataSource()
    {
        return ds;
    }
}

Define a test class to test the tool class

import utils.Druidutil;

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

public class Druidtest {
    public static void main(String[] args) {
        //Requirement: add a piece of data to the student table in DB2
        Connection conn=null;
        PreparedStatement prep=null;
        try {
            //1. Get the connection
            conn = Druidutil.getConnection();
            //2. Define SQL
            String SQL = "insert into student values (null,?,?,?)"; // use preparestatement
            //3. Get the preparestatement object
            prep = conn.prepareStatement(sql);
            prep.setInt(1,18);
            prep.setString(2,"Jennie");
            prep.setString(3,"191");
            //4. Execute SQL
            int result = prep.executeUpdate();
            //Print results
            System.out.println(result);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            Druidutil.close(prep,conn);
        }

    }
}

(added successfully)

To simplify the use of JDBC, we introduce a spring JDBC (JDBC template)

1. DML statement

import org.springframework.jdbc.core.JdbcTemplate;
import utils.Druidutil;

public class SpringJDBC {
    private static JdbcTemplate template;
    public static void main(String[] args) {

        //Execute DML statement
        //Import jar package to get JDBC template
        template = new JdbcTemplate(Druidutil.getDataSource());
        //Test1(); // modify
        //Test2(); // add operation
        //Test3(); // delete
    }

    private static void test3() {
        String sql="delete from user where id=?";
        int update = template.update(sql, 3);
        System.out.println(update);
    }

    private static void test2() {
        String sql="insert into user (id,name,password) values (?,?,?)";
        int update = template.update(sql, 4, "bts", "613");
        System.out.println(update);
    }

    private static void test1()
    {
        //Defining SQL statements
        String sql="update user set password='121' where id=1";
        //Execute SQL
        int update = template.update(sql);
        System.out.println(update);
    }
}

2. DQL statement　　

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import utils.Druidutil;

import java.util.List;
import java.util.Map;

public class SpringJDBC {
    private static JdbcTemplate template;

    public static void main(String[] args) {

        //Execute DQL statement
        //Import jar package to get JDBC template
        template = new JdbcTemplate(Druidutil.getDataSource());
        //Test1(); // query a record
        //Test2(); // query multiple records
        //Test3(); // repackage each data as EMP object
         //Test4(); // query the total number of records
    }

    private static void test4() {
        String sql="select count(*) from user";
        Long aLong = template.queryForObject(sql, Long.class);
        System.out.println(aLong);
    }

    private static void test3() {
        String sql="select * from user";
        List<emp> query = template.query(sql, new BeanPropertyRowMapper<emp>(emp.class));
        for (emp emp : query) {
            System.out.println(emp);
        }
    }

    private static void test2() {
        String sql="select * from user";
        List<Map<String, Object>> list = template.queryForList(sql);
        for (Map<String, Object> map : list) {
            System.out.println(map);
        }
    }

    private static void test1() {
        String sql="select * from user where id=?";
        Map<String, Object> map = template.queryForMap(sql, 3);
        System.out.println(map);
    }
}

Getting started with JDBC