JDBC---实践

 

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

public class Test {

    private static class User{
        private Integer id;
        private String name;

        public Integer getId() {
            return id;
        }

        public void setId(Integer id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    '}';
        }
    }

    static void statementTest(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "an314159");
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from user where id = "+ 23 + " and name = " + "'lili' or 1=1"); // select * from user where id = 23 and name = 'lili' or 1=1

            User user = new User();

            while (resultSet.next()){
                int id = resultSet.getInt(1);
                user.setId(id);
                String name = resultSet.getString(2);
                user.setName(name);
            }

            System.out.println(user);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    static void prepareStatementTest(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "an314159");

            // === 查询
//            String selectSql = "select * from user where id = ? and name = ?";
//            preparedStatement = connection.prepareStatement(selectSql);
//            preparedStatement.setObject(1, 23);
//            preparedStatement.setObject(2, "'lili' or 1=1");
//            resultSet = preparedStatement.executeQuery(); // select * from user where id = 23 and name = '''lili'' or 1=1' （name的值 作为 一个字符串 处理的）
//
//            User user = new User();
//
//            while (resultSet.next()){
//                int id = resultSet.getInt(1);
//                user.setId(id);
//                String name = resultSet.getString(2);
//                user.setName(name);
//            }
//
//            System.out.println(user);


            // === 新增
            // JDBC 默认 autoCommit = true
            connection.setAutoCommit(false);
//            connection.setTransactionIsolation(1);

            String insertSql = "insert into user(name,sex) values(?,?)";
            preparedStatement = connection.prepareStatement(insertSql);
            preparedStatement.setObject(1,"an4");
            preparedStatement.setObject(2,"an4");
            // true: if the first result is a ResultSet object;
            // false: if the first result is an update count or there is no result
            boolean insertResult = preparedStatement.execute();
            System.out.println(insertResult);

            int i = 1/ 0;

            // 显式 commit 事务
            connection.commit();



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (preparedStatement != null){
                try {
                    preparedStatement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    public static void main(String[] args) {

//        statementTest();

        prepareStatementTest();
    }

}