JDBC系列--登录练习和事务控制

package cn.caq.jdbc;
 import cn.caq.utils.JDBCutils;
 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.Scanner;
 /**
  * 需求：
  * 1.通过键盘录入用户名和密码
  * 2.判断用户是否登录成功
  */
 public class JDBCDemo08 {
     public static void main(String[]args){
         //1.键盘录入，接受用户名和密码
         Scanner scanner = new Scanner(System.in);
         System.out.println("请输入username：");
         String username = scanner.nextLine();
         System.out.println("请输入password");
         String password = scanner.nextLine();
         //2.调用方法
 //        JDBCDemo08 jdbcDemo08 = new JDBCDemo08();
 //        boolean login = jdbcDemo08.login(username, password);
         boolean flag = new JDBCDemo08().login(username, password);
         //3.判断结果，输出不同语句
         if (flag){
             System.out.println("Success!!!!!!");
         }else {
             System.out.println("username or passwd False!!!!!!!!");
         }
     }
     //登录方法
     public boolean login(String username,String password){
         if (username == null || password == null){
             return false;
         }
         Connection conn = null;
         Statement stmt = null;
         ResultSet rs = null;
         //1.连接数据库
         try {
             conn = JDBCutils.getConnection();
             //2.定义sql
             String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
             //3.获取执行sql的对象
             stmt = conn.createStatement();
             //4.执行sql语句
             rs = stmt.executeQuery(sql);
             //5.判断
             return rs.next();//有下一行返回true
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         } finally {
             JDBCutils.close(rs,stmt,conn);
         }
         return false;//如果出现异常返回false
     }
 }

package cn.caq.jdbc;
 import cn.caq.utils.JDBCutils;
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.SQLException;
 /**
  * 事务操作
  */
 public class JDBCDemo10 {
     public static void main(String[] args) {
         Connection conn = null;
         PreparedStatement pstmt1 = null;
         PreparedStatement pstmt2 = null;
         //1.获取连接
         try {
             conn = JDBCutils.getConnection();
             //开启事务
             conn.setAutoCommit(false);
             //2.定义sql
             //2.1张三 - 500
             String sql1 = "update account set balance = balance - ?  where id = ?";
             //2.1李四 + 500
             String sql2 = "update account set balance = balance + ?  where id = ?";
             //3.获取执行sql对象
             pstmt1 = conn.prepareStatement(sql1);
             pstmt2 = conn.prepareStatement(sql2);
             //4.设置参数
             pstmt1.setDouble(1, 500);
             pstmt1.setInt(2, 1);
             pstmt2.setDouble(1, 500);
             pstmt2.setInt(2, 2);
             //5.执行sql
             pstmt1.executeUpdate();
             //手动制造异常
             int i = 3 / 0;
             pstmt2.executeUpdate();
             //提交事务
             conn.commit();
         } catch (Exception throwables) {
             try {
                 if (conn != null) {
                     conn.rollback();
                 }
             } catch (SQLException e) {
                 e.printStackTrace();
             }
             throwables.printStackTrace();
         }
     }
 }