JavaBean和PreparedStatement

代码练习

Employee

package com.openlab.pojo;

//领域对象层

public class Employee {

   

    private String id;

    private String username;

    private double salary;

    private int age;

    private String depart;

   

    public Employee() {

         

    }


    public String getId() {

         return id;

    }


    public void setId(String id) {

         this.id = id;

    }


    public String getUsername() {

         return username;

    }


    public void setUsername(String username) {

         this.username = username;

    }


    public double getSalary() {

         return salary;

    }


    public void setSalary(double salary) {

         this.salary = salary;

    }


    public int getAge() {

         return age;

    }


    public void setAge(int age) {

         this.age = age;

    }


    public String getDepart() {

         return depart;

    }


    public void setDepart(String depart) {

         this.depart = depart;

    }


    @Override

    public String toString() {

         return "Employee [id=" + id + ", username=" + username + ", salary=" + salary + ", age=" + age + ", depart="

                  + depart + "]";

    }

}

工具类JDBCUtils

public List queryByAll(String sql){

       List list = null;

       try {

           conn = getConnection();

           st = conn.createStatement();

           ResultSet rs = st.executeQuery(sql);

           list = rsToList(rs);

       } catch (SQLException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }finally{

           jdbcClose(conn, st);

       }

        return list;

    }

    /**

     * 单挑记录的查询 Object

     */

    public Object queryByOne(String sql){

       Object obj = null;

       try {

           conn = getConnection();

           st = conn.createStatement();

           ResultSet rs = st.executeQuery(sql);

           obj = rsToObj(rs);

       } catch (SQLException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }finally{

           jdbcClose(conn, st);

       }

       return obj;

    }


public abstract Object rsToObj(ResultSet rs);

public abstract List rsToList(ResultSet rs);

业务逻辑层 Service

package com.openlab.service;

// 业务层接口

import java.util.List;

import com.openlab.pojo.Employee;

public interface EmployeeService {

    public Employee queryById(String id);

    public List queryByUserName(String username);

}

数据链路层Dao

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

//数据链路层

import com.openlab.pojo.Employee;

import com.openlab.utils.JDBCUtils;

public class EmployeeServcieDao extends JDBCUtils{

    @Override

    public Object rsToObj(ResultSet rs) {

       Object obj = null;

       try {

           Employee emp = new Employee();

           if(rs.next()){

              emp.setId(rs.getString("id"));

              emp.setUsername(rs.getString("username"));

              emp.setSalary(rs.getDouble("salary"));

              emp.setAge(rs.getInt("age"));

              emp.setDepart(rs.getString("depart"));

              obj = emp;

           }

       } catch (SQLException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }

       return obj;

    }

    @Override

    public List rsToList(ResultSet rs) {

       List list = new ArrayList();

       try {

           Employee emp = new Employee();

           while(rs.next()){

              emp.setId(rs.getString("id"));

              emp.setUsername(rs.getString("username"));

              emp.setSalary(rs.getDouble("salary"));

              emp.setAge(rs.getInt("age"));

              emp.setDepart(rs.getString("depart"));

              list.add(emp);

           }

       } catch (SQLException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }

       return list;

    }

}

业务层IMPL 具体业务逻辑的实现

package com.openlab.service.impl;


import java.util.List;


import com.openlab.dao.EmployeeServcieDao;

import com.openlab.pojo.Employee;

import com.openlab.service.EmployeeService;


public class EmployeeServiceImpl implements EmployeeService{


    EmployeeServcieDao edao = new EmployeeServcieDao();

    @Override

    public Employee queryById(String id) {

      

       String sql = "select * from tb7 where id='"+id+"'";

       Object obj = edao.queryByOne(sql);

       Employee em =(Employee) obj;

       return em;

    }


    @Override

    public List queryByUserName(String username) {

       String sql ="select * from tb7 where username='"+username+"'";

       List list =  edao.queryByAll(sql);

       return list;

    }


}

 

测试类

package com.openlab.test;


import java.util.List;


import org.junit.Test;


import com.openlab.pojo.Employee;

import com.openlab.service.impl.EmployeeServiceImpl;


import junit.framework.TestCase;


public class EmployeeServiceImplTest {


    EmployeeServiceImpl eimpl = new EmployeeServiceImpl();

    @Test

    public void testQueryById() {

       Employee e = eimpl.queryById("006");

       System.out.println(e);

       TestCase.assertEquals("qiweifeng", e.getUsername());

      

    }

   

    @Test

    public void testQueryByUserName(){

       List list = eimpl.queryByUserName("qiweifeng");

       System.out.println(list);

       TestCase.assertEquals(2, list.size());

      

    }


}

 PreparedStatement

PrepareStatement   先由PrepareStatement 和数据库连接 对字符串的预处理，处理结束以后再去执行SQL ，可以防止SQL注入

package com.openlab.utils;

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

public class preJDBCTest {
	
	public static void main(String[] args) {
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Connection conn = JDBCUtils.getConnection();
			
			String sql = "select * from tb7 where username=?";
			ps = conn.prepareStatement(sql);

			ps.setString(1, "' or 1 or '");
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				System.out.println(
						rs.getString("id")+"\t"+
						rs.getString("username")+"\t"+
						rs.getDouble("salary")+"\t"+
						rs.getInt("age")+"\t"+
						rs.getString("depart"));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}