使用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.youkuaiyun.com/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();
			}
		}
}