JDBC进阶

JDBC进阶

1 登录账户（简单实现）

（1）Dao层接口

public User findUserByName(String username);

（2）Dao层接口实现类

@Override
public User findUserByName(String username) {
	Connection con = JdbcUtil.getCon();
	Statement st =null;
	ResultSet rs =null;
	User user=null;
	String sql="SELECT * FROM user WHERE username='"+username+"'";
	System.out.println(sql);
	try {
		 st = con.createStatement();
		 rs=st.executeQuery(sql);
		while(rs.next()){
			user = new User();
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		JdbcUtil.close(con, st, rs);
	}
	return user;
}

（3）测试

@Test
public void test1() {
	String password="织席贩履";
	UserDaoImpl userDaoImpl = new UserDaoImpl();
	User user = userDaoImpl.findUserByName("刘备' OR '1=1");//"刘备' OR '1=1" 查询所有的数据
	if(user!=null){
		if(password.equals(user.getPassword()))
			System.out.println("登录成功");
		else
			System.out.println("密码错误");
	}
	else
		System.out.println("用户不存在，请注册");
}

2 PreparedStatement（登录账户）

Dao层接口实现类

@Override
public User findUserByNamePrepareStatement(String username) {
	Connection con = JdbcUtil.getCon();
	PreparedStatement pst = null;
	ResultSet rs = null;
	User user=null;
	String sql="SELECT * FROM user WHERE username=?";
	try {
		pst = con.prepareStatement(sql);
		pst.setString(1, username);
		rs = pst.executeQuery();
		while(rs.next()){
			user = new User();
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		JdbcUtil.close(con, pst, rs);
	}
	return user;
}

3 事务（转账）

（1）实体类

public class Account {
	private String userId;
	private BigDecimal money;
}

（2）Dao层接口

public interface AccountDao {
	/*
	 * 转账事物
	 */
	public void transformMoney(Account from,Account into);
	 /*
	  * 查询余额
	  */
	public BigDecimal findAccountMoney(String userid);
}

（3）Dao层接口实现类

转账业务

@Override
public void transformMoney(Account from, Account into) {
	Connection con = JdbcUtil.getCon();
	PreparedStatement pst =null;
	String sql="UPDATE account SET money=? WHERE userid=?";
	try {
		 con.setAutoCommit(false);
		 pst=con.prepareStatement(sql);
		 if(findAccountMoney(from.getUserId()).compareTo(from.getMoney())>=0){
			 pst.setBigDecimal(1, findAccountMoney(from.getUserId()).subtract(from.getMoney()));
			 pst.setString(2, from.getUserId());
			 pst.executeUpdate();
			 //System.out.println(1/0);
			 pst.setBigDecimal(1, findAccountMoney(into.getUserId()).add(into.getMoney()));
			 pst.setString(2, into.getUserId());
			 pst.executeUpdate();
			 con.commit();
			 System.out.println("转账成功");
		 }else{
			 System.out.println("余额不足");
		 }
	} catch (Exception e) {
		try {
			con.rollback();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		e.printStackTrace();
	}finally{
		JdbcUtil.close(con, pst,null);
	}
}

查询余额

@Override
public BigDecimal findAccountMoney(String userid) {
	Connection con = JdbcUtil.getCon();
	PreparedStatement pst = null;
	ResultSet rs = null;
	BigDecimal money=null;
	String sql="SELECT * FROM account WHERE userid=?";
	try {
		pst = con.prepareStatement(sql);
		pst.setString(1, userid);
		rs = pst.executeQuery();
		while(rs.next()){
			money=new BigDecimal(rs.getString("money"));
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		JdbcUtil.close(con, pst, rs);
	}
	return money;
}

（4）测试

/*
*转账
*/
@Test
public void testMoney(){
	Account from=new Account();
	from.setUserId("过儿");
	from.setMoney(new BigDecimal(500));
	Account into=new Account();
	into.setUserId("小龙女");
	into.setMoney(new BigDecimal(500));
	AccountDaoImpl accountDaoImpl = new AccountDaoImpl();
	accountDaoImpl.transformMoney(from, into);
}

4 获取主键

（1）Dao层接口

public void getKey(User user);

（2）Dao层接口实现类

@Override
public void getKey(User user) {
	Connection con = JdbcUtil.getCon();
	String sql="INSERT INTO user (username,password) VALUES (?,?)";
	try {
		PreparedStatement pst = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		pst.setString(1, user.getUsername());
		pst.setString(2, user.getPassword());
		pst.executeUpdate();
		ResultSet rs = pst.getGeneratedKeys();
		while(rs.next()){
			System.out.println(rs.getLong(1));
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
}

（4）测试

@Test
public void testKey(){
	User user = new User();
	user.setUsername("庞统");
	user.setPassword("卒落凤坡");
	UserDaoImpl userDaoImpl = new UserDaoImpl();
	userDaoImpl.getKey(user);
}

5 连接池

public static Properties  p = new Properties();
	private static DataSource ds =null;
	static{
		try {
			InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
			p.load(is);
			//加载驱动
			//Class.forName(p.getProperty("driver"));
			ds=BasicDataSourceFactory.createDataSource(p);
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
	public static Connection getCon(){
		/*Connection cn=null;
		try {
			//加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			//获取链接
			cn = DriverManager.getConnection("jdbc:mysql://localhost:3306/person", "root", "1234");
		} catch (Exception e) {
			e.printStackTrace();
		}*/
		
		
		/*Connection cn=null;
		try {
			//获取链接
			cn = DriverManager.getConnection(p.getProperty("url"), p.getProperty("username"),p.getProperty("password"));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cn;*/
		Connection cn=null;
		try {
			cn = ds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cn;
	}

Jdbc.properties配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/person
username=root
password=1234

6 Mysql安全模式

mysql有个叫SQL_SAFE_UPDATES的变量，为了数据库更新操作的安全性
直接执行：SET SQL_SAFE_UPDATES=0; 缺省session 只针对当前会话有效
SET SQL_SAFE_UPDATES=1; 没有限制条件不能运行 限制条件为主键 当前会话
设置全局SET global SQL_SAFE_UPDATES=1;