使用JDBC实现查询和转账操作

工具:Myeclipse(Eclipse):
知识点:如何防止SQL注入攻击和JDBC中的修改/删除操作
jar包:
commons-collections4-4.0.jar
commons-dbcp-1.4.jar
commons-pool-1.6.jar
Oracle 11g 11.2.0.1.0 JDBC_ojdbc6.jar

代码都是基于自己写的连接池技术,,已经分享过了
url:http://blog.csdn.net/coder_hello_world/article/details/78515974

查询没有什么好说的

就说转账中注意的:
我们认为把转账和收款人两次作为一次事物来对待,
所以当用户输入错误的信息的时候,我们不能对用户的金额进行修改
也就是先执行改转账人的SQL语句,如果用户输入的信息错误,
我们不能对用户的信息进行修改,我们应该把两者的同时成功算成一次事物来处理,
所以我们必须先关闭自动提交事物,该成手动提交事物.
当收款人收到金额的时候,我们在对事物进行提交,否则回滚


还有问题.提交的事物改为false,不自动提交,如果下一个用户来了,该怎么执行

.无法进行下一步.所以我们在关闭资源的时候,将事物改为自动提交

package com.csdn.jdbcdemo.date2017_11_17;import java.io.Reader;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Scanner;import org.apache.commons.dbcp.BasicDataSource;import com.csdn.jdbcdemo.date2017_11_12.BasicDataSourceDemo;/** * 使用JDBC实现的查询和转账操作 * @author 89155 * */public class JDBCDemo4 {public static void main(String[] args) {System.out.println("1.查询       2.转账   ");System.out.println("请输入选项");Scanner scanner= new Scanner(System.in);String options= scanner.next();if(options.matches("[1-3]{1}")){int options2 = Integer.parseInt(options);switch(options2){case 1://查询selectDemo(scanner);break;case 2://转账transfer(scanner);break;}}else{System.out.println("输入有误!请重新输入!");}}//查询操作private static void selectDemo(Scanner scanner) {System.out.println("输入你要查询的用户名:");String username= scanner.next();try{Connection connection= BasicDataSourceDemo.getConnection();String selectSql = "SELECT * FROM EMP"+ " WHERE"+ " LOWER(EMPNP) = LOWER(?)";//String selectSql = "SELECT * FROM EMP";PreparedStatement pstate= connection.prepareStatement(selectSql);pstate.setString(1,username);;ResultSet rs = pstate.executeQuery();if(rs.next()){System.out.println("此用户的信息如下:");System.out.println(rs.getInt(1)+"\n"+rs.getString(2));}else{System.out.println("没有此用户!");}rs.close();pstate.close();}catch(Exception e){e.printStackTrace();}finally{BasicDataSourceDemo.closeConnection();}}//转账操作private static void transfer(Scanner scanner) {System.out.println("输入您的账户:");String fromuser = scanner.next();System.out.println("输入收款人的账户:");String toUser = scanner.next();System.out.println("输入您要转账的金额:");String money = scanner.next();System.out.println("输入你的密码:");String password = scanner.next();try{Connection connection= BasicDataSourceDemo.getConnection();//将事物自动提交改为不自动提交connection.setAutoCommit(false);//查询用户是否存在String sql = "SELECT * FROM USER_INFODEMO "+ "WHERE "+ "LOWER(USERNAME) = LOWER(?) "+ "AND "+ "PASSWORD = ?";PreparedStatement pstate = connection.prepareStatement(sql);pstate.setString(1, fromuser);pstate.setString(2, password);ResultSet rs = pstate.executeQuery();if(rs.next()){//获取该用户的资产int sal = rs.getInt("SAL");//判断用户输入金额是否高于输入金额if(sal>Integer.parseInt(money)){//实现账户的金额减少String sql2 = "UPDATE USER_INFODEMO "+ "SET "+ "SAL = SAL - ? "+ "WHERE "+ "USERNAME =?";PreparedStatement pstate2 = connection.prepareStatement(sql2);pstate2.setString(1,money);pstate2.setString(2,fromuser);//实现收款人收到金额if(pstate2.executeUpdate()>0){String sql3 = "UPDATE USER_INFODEMO "+ "SET "+ "SAL = SAL + ? "+ "WHERE "+ "USERNAME =?";PreparedStatement pstate3= connection.prepareStatement(sql3);pstate3.setString(1,money);pstate3.setString(2,toUser);if(pstate3.executeUpdate()>0){System.out.println("转账成功!");//两次都执行成功,都执行提交connection.commit();pstate2.close();pstate3.close();}else{System.out.println("转账失败!");//有一个执行失败,进行回滚connection.rollback();pstate2.close();pstate3.close();}}else{System.out.println("没有此用户:"+toUser);}}else{System.out.println("此账户余额不足!"+fromuser);}}else{System.out.println("您的账户或者密码错误!");}pstate.close();}catch(Exception e){e.printStackTrace();}finally{BasicDataSourceDemo.closeConnection();}}}