JDBC learning notes

JDBC

Database driven

In order to simplify the unified operation of database by developers, SUN company provides a Java database operation specification, called JDBC

The implementation of the specification is done by specific manufacturers

For developers, you only need to master the JDBC interface

The simplest JDBC program

  1. Create test database

    CREATE TABLE users(
      `id` INT PRIMARY KEY,
      `name` VARCHAR(40),
      `password` VARCHAR(40),
      `email` VARCHAR(60),
      `birthday` DATE
    );
    
    INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)
    VALUES(1,'Zhang San','123456','zs@sina.com','1980-12-04'),
    (2,'Li Si','123456','lisi@sina.com','1981-12-04'),
    (3,'Wang Wu','123456','wangwu@sina.com','1982-12-04');
    
  2. Import jar package

    Create a new lib, copy the jar package, and right-click add as library

  3. Write test code

    1) Load driver

    2) Connect to database DriverManager

    3) Object Statement executing SQL

    4) Return result set

    5) Release connection

    public class JdbcFirstDemo {
        public static void main(String[] args) throws ClassNotFoundException, SQLException {
            //1. Load drive
            Class.forName("com.mysql.cj.jdbc.Driver");
    
            //2. User information and url
            //useUnicode=true&charactorEncoding=utf8&useSSL=true
            String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&charactorEncoding=utf8&useSSL=true&serverTimezone=UTC";
            String usename = "root";
            String password = "123456";
    
            //3. Connect to the database DriverManager
            Connection connection = DriverManager.getConnection(url, usename, password);
    
            //4. Execute SQL object Statement
            Statement statement = connection.createStatement();
    
            //5. Return result set
            String sql = "SELECT * FROM users";
            ResultSet resultSet = statement.executeQuery(sql);
    
            while (resultSet.next()){
                System.out.println("id"+resultSet.getObject("id"));
                System.out.println("name"+resultSet.getObject("name"));
                System.out.println("password"+resultSet.getObject("password"));
                System.out.println("email"+resultSet.getObject("email"));
                System.out.println("birthday"+resultSet.getObject("birthday"));
            }
            //6. Release the connection
            resultSet.close();
            statement.close();
            connection.close();
        }
    }
    

Object interpretation in JDBC

DriverManager

//1. Load drive
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//This writing method is recommended to load the driver
Class.forName("com.mysql.jdbc.Driver");

Connection connection = DriverManager.getConnection(url, userName, passWord);
// connection represents the database
// Database settings auto commit
// Transaction commit
// Transaction rollback
connection.setAutoCommit(true);
connection.commit();
connection.rollback();

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";

// mysql default port 3306
// Protocol: / / host address: port number / database name? Parameter1 & parameter2 & parameter3
// oracle default port 1521
// jdbc:oracle:thin:@localhost:1521:sid

Statement execution sql object, PreparedStatement execution sql object

String sql = "SELECT * FROM users";//Write SQL

statement.executeQuery();//Execute query and return ResultSet
statement.executeUpdate();//Add, delete and modify all use this to return the number of affected rows
statement.execute();//Execute any SQL

Gets the specified data type

//Use without knowing the column type
resultSet.getObject();
//If you know the column type, use the specified type
resultSet.getString();
resultSet.getInt();
resultSet.getDouble();
resultSet.getBigDecimal();
resultSet.getFloat();
resultSet.getDate();
//...

Traversal, pointer

resultSet.beforeFirst();//Move to the front
resultSet.afterLast();//Move to the back
resultSet.next();//Move to next data
resultSet.previous();//Move to previous line
resultSet.absolute(row);//Move to specified row

Release resources

resultSet.close();
statement.close();
connection.close();//Consumption of resources

Statement object

  1. Extraction tool class

    public class JdbcUtils {
    
        private static String driver = null;
        private static String url = null;
        private static String username = null;
        private static String password = null;
    
        static {
            try {
                InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
                Properties properties = new Properties();
                properties.load(in);
    
                driver = properties.getProperty("driver");
                url = properties.getProperty("url");
                username = properties.getProperty("username");
                password = properties.getProperty("password");
    
                //The driver is loaded only once
                Class.forName(driver);
    
    
            } catch (IOException | ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    
        //Get connection
        public static Connection getConnection() throws SQLException {
            return DriverManager.getConnection(url, username, password);
        }
    
        //Release connection
        public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
    
            if(connection!=null){
                connection.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(resultSet!=null){
                resultSet.close();
            }
        }
    }
    
    
  2. Add, delete, modify and query

    public class TestInsert {
        public static void main(String[] args) throws SQLException {
    
            Connection conn = null;
            Statement st = null;
            ResultSet rs = null;
    
            try {
                conn = JdbcUtils.getConnection();//Get database connection
                st = conn.createStatement();//Get the execution object of SQL
                String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)" +
                        "VALUES(4,'liuuuu','123456','liuuuu@sina.com','1998-12-04')";
                int i = st.executeUpdate(sql);
                if(i>0){
                    System.out.println("Insert succeeded!");
                }
    
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                JdbcUtils.release(conn,st,rs);
            }
    
        }
    }
    
    
    public class TestDelete {
        public static void main(String[] args) throws SQLException {
    
            Connection conn = null;
            Statement st = null;
            ResultSet rs = null;
    
            try {
                conn = JdbcUtils.getConnection();//Get database connection
                st = conn.createStatement();//Get the execution object of SQL
                String sql = "DELETE FROM users WHERE id =3";
                int i = st.executeUpdate(sql);
                if(i>0){
                    System.out.println("Delete succeeded!");
                }
    
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                JdbcUtils.release(conn,st,rs);
            }
    
        }
    }
    
    public class TestUpdate {
        public static void main(String[] args) throws SQLException {
    
            Connection conn = null;
            Statement st = null;
            ResultSet rs = null;
    
            try {
                conn = JdbcUtils.getConnection();//Get database connection
                st = conn.createStatement();//Get the execution object of SQL
                String sql = "UPDATE users SET `name`='Two dogs' WHERE `name`='liuuuu'";
                int i = st.executeUpdate(sql);
                if(i>0){
                    System.out.println("Update succeeded!");
                }
    
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                JdbcUtils.release(conn,st,rs);
            }
    
        }
    }
    
    public class TestQuery {
        public static void main(String[] args) throws SQLException {
    
            Connection conn = null;
            Statement st = null;
            ResultSet rs = null;
    
            try {
                conn = JdbcUtils.getConnection();//Get database connection
                st = conn.createStatement();//Get the execution object of SQL
                String sql = "SELECT * FROM users;";
                rs = st.executeQuery(sql);
                while (rs.next()){
                    System.out.println("id"+rs.getObject("id"));
                    System.out.println("name"+rs.getObject("name"));
                    System.out.println("password"+rs.getObject("password"));
                    System.out.println("email"+rs.getObject("email"));
                    System.out.println("birthday"+rs.getObject("birthday"));
                }
    
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                JdbcUtils.release(conn,st,rs);
            }
        }
    }
    

SQL injection

SQL injection means that the web application does not judge or filter the legitimacy of the user's input data. The attacker can add additional SQL statements at the end of the query statements defined in advance in the web application, and realize illegal operations without the knowledge of the administrator, so as to deceive the database server to execute unauthorized arbitrary queries, So as to further obtain the corresponding data information.

public class SqlInjection {
    public static void main(String[] args) throws SQLException {
        //login("liuuuu","123456");
        login(" 'or '1=1"," 'or'1=1");
    }
    //Login service
    public static void login(String username,String password) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();//Get database connection
            st = conn.createStatement();//Get the execution object of SQL
            //SELECT * FROM users WHERE `name` = 'liuuuu' AND `password`='123456'
            String sql = "SELECT * FROM users WHERE name ='"+username+"'AND password='"+password+"'";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println("name="+rs.getObject("name"));
                System.out.println("password="+rs.getObject("password"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

PreparedStatement object

The essence of PreparedStatement in preventing sql injection

The parameters passed in are treated as characters. If there are escape characters, they are directly escaped

Add, delete, modify and query

public class TestInsert {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();//Get database connection

            //difference
            //use? Replace parameters as placeholders
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)VALUES(?,?,?,?,?)";
            st = conn.prepareStatement(sql);//Precompiled SQL, write SQL first, and then do not execute
            //Assign parameters manually
            st.setInt(1,3);//id
            st.setString(2,"Li Lei");
            st.setString(3,"123444");
            st.setString(4,"4545@qq.com");
            //Note: sql.Data database java.sql.Date()
            //        Util.date Java new date(). Gettime() gets the timestamp
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

            //implement
            int i = st.executeUpdate();//Returns the number of rows affected

            if(i>0){
                System.out.println("Insert successful!");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}
public class TestDelete {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();//Get database connection

            //difference
            //use? Replace parameters as placeholders
            String sql = "DELETE FROM users WHERE id =?";
            st = conn.prepareStatement(sql);//Precompiled SQL, write SQL first, and then do not execute
            //Assign parameters manually
            st.setInt(1,3);//id
            //implement
            int i = st.executeUpdate();//Returns the number of rows affected
            if(i>0){
                System.out.println("Delete succeeded!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}
public class TestUpdate {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();//Get database connection

            //difference
            //use? Replace parameters as placeholders
            String sql = "UPDATE users SET `name`=? WHERE `name`=?";
            st = conn.prepareStatement(sql);//Precompiled SQL, write SQL first, and then do not execute
            //Assign parameters manually
            st.setString(1,"bobo");
            st.setString(2,"liuuuu");
            //implement
            int i = st.executeUpdate();//Returns the number of rows affected
            if(i>0){
                System.out.println("Update succeeded!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}
public class TestQuery {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();//Get database connection

            //difference
            //use? Replace parameters as placeholders
            String sql = "SELECT * FROM users WHERE id = ?";
            st = conn.prepareStatement(sql);//Precompiled SQL, write SQL first, and then do not execute
            //Assign parameters manually
            st.setInt(1,1);

            //implement
            rs = st.executeQuery();//Returns the number of rows affected
            while(rs.next()){
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

affair

Code implementation:

  1. Start the transaction conn.setAutoCommit(false);
  2. After a group of transactions are executed, commit the transaction
  3. Rollback statements can be defined in catch statements, but rollback fails by default
public class TestTransaction1 {
    public static void main(String[] args)  {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false); //Open transaction

            //Turn off database auto commit
            conn.setAutoCommit(false);

            String sql1 = "update account set money = money-100 where name ='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            int x = 1/0;//report errors

            String sql2 = "update account set money = money+100 where name ='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //After the business is completed, submit the transaction
            conn.commit();
            System.out.println("success");
        }catch (SQLException throwables) {
            try {
                conn.rollback();//Roll back the transaction if it fails
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(conn,st,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

Database connection pool

General process of database operation:

Connect - > finish execution - > release wasted system resources

Principle of database connection pool:

The database connection is stored in memory as an object. When users need to access the database, they do not establish a new connection, but take out an established idle connection object from the connection pool. After use, instead of closing the connection, the user puts the connection back into the connection pool for the next request. The establishment and disconnection of these connections are managed by the connection pool itself.

realization

Open source data source implementation:

DBCP C3P0 Druid: Ali

DBCP

Required jar packages:

commons-pool2-2.6.2.jar commons-dbcp2-2.4.0.jar commons-logging-1.2.jar

Network disk link: https://pan.baidu.com/s/1aV-q0XTSJmlr9ttU1-U3Tw
Extraction code: nj1n

Tool class

public class JdbcUtils_DBCP {

    private static DataSource dataSource = null;
    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //Create data source factory schema - > create
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //Get connection
    public static Connection getConnection() throws SQLException {
        return  dataSource.getConnection();//Get connection from data source
    }

    //Release connection
    public static void release(Connection connection,PreparedStatement statement,ResultSet resultSet) throws SQLException {
        if(connection!=null){
            connection.close();
        }
        if(statement!=null){
            statement.close();
        }
        if(resultSet!=null){
            resultSet.close();
        }
    }
}

test

public class TestDBCP {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils_DBCP.getConnection();//Get database connection

            //difference
            //use? Replace parameters as placeholders
            String sql = "SELECT * FROM users";
            st = conn.prepareStatement(sql);//Precompiled SQL, write SQL first, and then do not execute


            //implement
            rs = st.executeQuery();
            while(rs.next()){
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(conn,st,null);
        }
    }
}

Tags: Java Database MySQL

Posted on Sat, 25 Sep 2021 05:58:54 -0400 by daprezjer