【DAO】

DAO层作用

• 定义操作数据库的逻辑

DAO实现

• 建Dao包
• 写接口（要操作什么表就叫xxDao）
• 在接口中定义方法
• 建DaoImpl包（implement）用来实现接口中的方法
• 在需要使用的地方

XXXDao dao = new XXXDaoImp

登陆案例

package com.zd.Dao;

public interface UserDao {
  /**
   * 登陆
   * @param username
   * @param password
   */
  void login(String username,String password);
}

package com.zd.DaoImpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.zd.Dao.UserDao;
import com.zd.util.JDBCUtil;

public class UserDaoImpl implements UserDao{

  @Override
  public void login(String username, String password) {
  	Connection conn = null;
  	Statement st = null;
  	ResultSet rs = null;
  	try {
  		 conn = JDBCUtil.getConn();
  		 st = conn.createStatement();
  		 String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
  		 rs = st.executeQuery(sql);
  		 if(rs.next()){
  			 System.out.println("登陆成功");
  		 }else{
  			 System.out.println("登陆失败");
  		 }
  		 
  	} catch (Exception e) {
  		e.printStackTrace();
  	}finally{
  		JDBCUtil.release(conn, st, rs);
  	}
  }

}

package com.zd.test;

import com.zd.Dao.UserDao;
import com.zd.DaoImpl.UserDaoImpl;

public class Test {
  public static void main(String[] args) {
  	UserDao dao = new UserDaoImpl();
  	dao.login("aaa", "123");
  }
}

登陆案例的Statement安全问题 —— PrepareStatement

   	UserDao dao = new UserDaoImpl();
   	dao.login("aaa", "123' or '1=1");

  String sql = "select * from user where username = '"+username+"' and password = '"+password+"' or 1=1";

• 这样sql语句成立
• Statement执行，是拼接sql语句，先拼接，再执行（若里面含有关键字 or 等会认为是关键字）
• PrepareStatement
• 预先对sql语句进行处理，？（占位符）位置一律当字符串处理
• 注意下标从1开始

String sql = "select * from user where username =? and password =?";
   		 PreparedStatement ps = conn.prepareStatement(sql);
   		 ps.setString(1, username);
   		 ps.setString(2, password);
   		 rs = ps.executeQuery();

package com.zd.DaoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import com.zd.Dao.UserDao;
import com.zd.util.JDBCUtil;

public class UserDaoImpl implements UserDao{

   @Override
   public void login(String username, String password) {
   	Connection conn = null;
   	PreparedStatement ps = null;
   	ResultSet rs = null;
   	try {
   		 conn = JDBCUtil.getConn();
   		 String sql = "select * from user where username =? and password =?";
   		 ps = conn.prepareStatement(sql);
   		 ps.setString(1, username);
   		 ps.setString(2, password);
   		 rs = ps.executeQuery();
   		 
   		 if(rs.next()){
   			 System.out.println("登陆成功");
   		 }else{
   			 System.out.println("登陆失败");
   		 }
   		 
   	} catch (Exception e) {
   		e.printStackTrace();
   	}finally{
   		JDBCUtil.release(conn, ps, rs);
   	}
   }

   @Override
   public void add(String username, String password, String phonenumber, String role) {
   	Connection conn = null;
   	PreparedStatement ps = null;
   	try {
   		conn = JDBCUtil.getConn();
   		String sql = "insert into user values(null,?,?,?,?)";
   		ps = conn.prepareStatement(sql);
   		ps.setString(1, username);
   		ps.setString(2, password);
   		ps.setString(3, phonenumber);
   		ps.setString(4, role);
   		int result = ps.executeUpdate();
   		if(result > 0){
   			System.out.println("添加成功");
   		}else{
   			System.out.println("添加失败");
   		}
   	} catch (Exception e) {
   		e.printStackTrace();
   	}finally{
   		JDBCUtil.release(conn, ps);
   	}
   }

   @Override
   public void delete(String name) {
   	// TODO Auto-generated method stub
   	
   }

   @Override
   public void update(String password, int userid) {
   	Connection conn = null;
   	PreparedStatement ps = null;
   	try {
   		conn = JDBCUtil.getConn();
   		String sql = "update user set password = ? where uid = ?";
   		ps = conn.prepareStatement(sql);
   		ps.setString(1, password);
   		ps.setInt(2, userid);
   		int result = ps.executeUpdate();
   		if(result > 0){
   			System.out.println("修改密码成功");
   		}else{
   			System.out.println("修改密码失败");
   		}
   	} catch (Exception e) {
   		e.printStackTrace();
   	}finally{
   		JDBCUtil.release(conn, ps);
   	}
   }

}