JavaWeb作业02

1.抽象类为什么不能创建对象

• 抽象类拥有至少一个抽象方法，被abstract关键字修饰，不能直接实例化。
• 抽象类是不完整的，它只能用作基类。在面向对象方法中，抽象类主要用来进行类型隐藏和充当全局变量的角色。
• 抽象类相当于方法的声明而不是实现，抽象方法没有具体的实现语句，所以不能创建对象。

2.SQL查询语句关键字的执行顺序

• from       where       group by      having      select      order by（从左往右执行）

3.Statement和PreparedStatement的区别

• PreparedStatement是预编译的，批量处理时效率提高，同时阻止了SQL注入，提高了安全性。
• PreparedStatement 对象的开销比Statement大，对于一次性操作的不如使用Statament好。

4.JavaBean和PreparedStatement

employee.java 

package Bean.pojo;

public class Employee {
    private String id;
    private String username;
    private double salary;
    private int age;
    private String depart;

    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 + '\'' +
                '}';
    }
}

EmployeeServiceDao.java

package Bean.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Bean.pojo.Employee;
import web.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) {
            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) {
            e.printStackTrace();
        }
        return list;
    }
}

EmployeeService.java

package Bean.service;

import java.util.List;


import Bean.pojo.Employee;

public interface EmployeeService {

    public Employee queryById(String id);

    public List queryByUserName(String username);


}

EmployeeServiceImpl.java

package Bean.service.impl;

import java.util.List;

import Bean.dao.EmployeeServcieDao;
import Bean.pojo.Employee;
import Bean.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;
    }

}

EmployeeServiceImplTest.java

package Bean.test;

import java.util.List;

import org.junit.Test;
import junit.framework.TestCase;

import Bean.pojo.Employee;
import Bean.service.impl.EmployeeServiceImpl;

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());
    }
}

ProparedStatement封装

update

 public void update(String sql,Object...args){
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++){
                ps.setObject(i+1,args[i]);
            }
            int bRet = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
    }
}

query

//import rect.RectUtils;
public <T> T query(Class<T> clazz,String sql,Object...args){
        T entity = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            Map<String, Object> values = new HashMap<>();

            if(rs.next()){
                for(int i = 0; i < rsmd.getColumnCount(); i++){
                    //列的名称
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    //列多对应的value
                    Object columnValue = rs.getObject(i + 1);

                    values.put(columnLabel, columnValue);
                }
            }

            if(values.size() > 0){
                entity = clazz.newInstance();
                for(Map.Entry<String, Object> entry: values.entrySet()){
                    String fieldName = entry.getKey();
                    Object value = null;
                    if (entry.getValue() instanceof Long) {
                        value = Integer.parseInt(entry.getValue().toString());
                    }else{
                        value = entry.getValue();
                    }
                    RectUtils.setFieldValue(entity, fieldName, value);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return entity;
    }