Getting started with JDBC

Time:2021-1-21

Getting started with JDBC

 

Detailed explanation

1.0DriverManager

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);
    }
}

 

  

 

Recommended Today

Practice of query operation of database table (Experiment 3)

Following the previous two experiments, this experiment is to master the use of select statements for various query operations: single table query, multi table connection and query, nested query, set query, to consolidate the database query operation.Now follow Xiaobian to practice together!Based on the data table (student, course, SC, teacher, TC) created and inserted in […]