JDBC预编译，数据库事务

JDBC

Statement的不足

1.大量的拼接，可读性低

 2.sql注入

Connection conn = null;
        Statement stmt = null;
        ResultSet re = null;
        conn = GetConnection.test();
        stmt = conn.createStatement();
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = sc.next();
        System.out.println("请输入用户密码");
        String password = sc.next();
        String sql = "select username,password from user where username = '"
                + username + "' and password = " + password + "'";
        re = stmt.executeQuery(sql);
        if(re.next()){
            System.out.println("登录成功" + username);
        }else {
            System.out.println("error");
        }

 

sql注入

通过字符串拼接，可以的一个恒等的sql语句，可以跳过某些判断

select * from user where username = 'zxcvzxcvzxcv' and password = 'b' or '1' = '1'

 

PreparedStatement

1.通过conn获取的对象

2.是Statement接口的子接口

3.sql语句中可以传参，用？占位，通过setXXX方法给？赋值

4.提高性能

5.可以避免sql注入

        Connection conn = null;
        PreparedStatement pstmt = null;

        String sql = "update teacher set name = ? where id =?";
        //预编译
        conn = GetConnection.test();
        pstmt = conn.prepareStatement(sql);
        //给占位符赋值，根据位置
        pstmt.setString(1,"jj");
        pstmt.setInt(2,6);

        //执行sql
        int i = pstmt.executeUpdate();
        System.out.println(i);
        conn.close();
        pstmt.close();

package jdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class GetConnection {
    public static Connection test(){
        Connection conn = null;
        Properties properties = new Properties();
        try {
            properties.load(GetConnection.class.getClassLoader().getResourceAsStream("db.properties"));
            String url = properties.getProperty("mysql.url");
            String driverName = properties.getProperty("mysql.driverName");
            String username = properties.getProperty("mysql.username");
            String password = properties.getProperty("mysql.password");
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, username, password);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        return conn;
    }

}

 

getMetaData

Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet re = null;
        List<StudentScoreCourse> list = new ArrayList<>();

        String sql = "SELECT * from student";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        re = pstmt.executeQuery();

        /**
         * 结果集
         * 获取元数据：表格本身的数据
         *      表格的列名，结果集的列名
         */
        ResultSetMetaData metaData = re.getMetaData();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            System.out.println(metaData.getColumnName(i));
        }

 

数据库事务

mysql数据库引擎

1.在mysql中只用使用了Innodb引擎的才支持事务

2.事务处理可以用来维护数据的完整性，保证sql语句要么全部执行，要么全部不执行

3.发生在DML中（增删改）

事务四大特征ACID

1.原子性   A

  一个事务要么全部完成，要么全部不执行

2.一致性 C

  事务开始之前和事务结束之后，数据库的完整性没有被破坏

3.隔离性  I

  数据库允许多个事务同时处理，每个事务之间相隔离

4.持久型 D

  事务结束以后，对数据的增删改是永久性的

 

提交事务，回滚事务

1.事务一旦提交，不能回滚

2.当一个连接对象被创建时，默认情况下自动提交事务

3.关闭连接时，数据会自动提交事务

 

操作事务的步骤

1.关闭事务的自动提交

 

事务回滚

conn.rollback();//事务回滚

当做出增删改操作，把变化发生在内存中，提交事务，才会真正提交给事务

 

 

案例：

package jdbc.shiwu;

import jdbc.JDBCUtil;

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

public class Test_4 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pt1 = null;
        PreparedStatement pt2 = null;
        conn = JDBCUtil.getConnection();
        try {
            conn.setAutoCommit(false);//关闭自动提交

        // conn.setAutoCommit(true);//自动提交

        String sql = "update balance set name =  name - 100 where id = 1";

            pt1 = conn.prepareStatement(sql);
            pt1.executeUpdate();

            //int i = 1/0;

            String sql1 = "update balance set name =  name + 100 where id = 2";
            pt2 = conn.prepareStatement(sql1);
            pt1.executeUpdate();

            conn.commit();//提交事务

            System.out.println("success");
        } catch (SQLException e) {
            try {
                conn.rollback();//事务回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }

    }
}