通用增删改查

 

链接数据库接口

package com.db;

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

public interface DBConn {

    Connection getConn();
    
    public void close(Connection con);
    public void close(PreparedStatement pstmt);
    public void close(ResultSet rs);
    public void close(Statement stmt);
    public void closeAll(ResultSet rs,PreparedStatement pstmt,Connection con);
}

链接数据库

package com.db.impl;

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

import com.db.DBConn;


public class DBConnJdbc implements DBConn{
    
    private String driver="com.mysql.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/ebook?useUnicode=true&characterEncoding=utf-8";
    private String user = "root";
    private String password = "11111";
    
    public Connection getConn(){
        Connection con = null;
        try{
            Class.forName(driver);
            con=DriverManager.getConnection(url,user,password);            
        }catch(Exception e){
            e.printStackTrace();
        }
        return con;
    }
    
    public void close(Connection con){
        try{
            if(con != null){
                con.close();
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
    
    public void close(PreparedStatement pstmt){
        try{
            if(pstmt != null){
                pstmt.close();
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
    
    public void close(ResultSet rs){
        try{
            if(rs != null){
                rs.close();
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
    
    public void close(Statement stmt){
        try{
            if(stmt != null){
                stmt.close();
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

    
    public void closeAll(ResultSet rs,PreparedStatement pstmt,Connection con) {
        try {
            if(rs != null) {
                rs.close();
                rs = null;
            }
            if(pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if(con != null) {
                con.close();
                con = null;
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

package com.db.impl;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

import com.db.DBConn;

public class DBConnJdbcProp implements DBConn{

    private static Properties prop = new Properties();
    

    static{
        InputStream in = null;
        Class c = DBConnJdbcProp.class;
        ClassLoader loader = c.getClassLoader();
        in = loader.getResourceAsStream("jdbc.properties");
        try {
            prop.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            try {
                if(in != null){            
                in.close();
                }
            } catch (IOException e) {
                e.printStackTrace();            
            }
        }    
    }
    
    @Override
    public Connection getConn(){
        String driver = prop.getProperty("driver");
        String url = prop.getProperty("url");
        String user = prop.getProperty("user");
        String password = prop.getProperty("password");        
        Connection con = null;
        try{
            Class.forName(driver);
            con=DriverManager.getConnection(url,user,password);            
        }catch(Exception e){
            e.printStackTrace();
        }
        return con;
    }

    @Override
    public void close(Connection con){
        try{
            if(con != null){
                con.close();
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
    
    @Override
    public void close(PreparedStatement pstmt){
        try{
            if(pstmt != null){
                pstmt.close();
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
    
    @Override
    public void close(ResultSet rs){
        try{
            if(rs != null){
                rs.close();
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
    
    @Override
    public void close(Statement stmt){
        try{
            if(stmt != null){
                stmt.close();
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void closeAll(ResultSet rs, PreparedStatement pstmt, Connection con) {
        try {
            if(rs != null) {
                rs.close();
                rs = null;
            }
            if(pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if(con != null) {
                con.close();
                con = null;
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
        
    }    
}

BaseDao

package com.dao;

import java.util.List;

public interface BaseDao<Entity> {
    
    public int add(Entity obj) throws Exception;
    public int delete(int id) throws Exception;
    public int update(Entity obj) throws Exception;
    public Entity get(int id) throws Exception;
    public List<Entity> getList() throws Exception;
    
}

package com.dao.impl;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.dao.BaseDao;
import com.db.DBConn;
import com.db.impl.DBConnJdbc;

public class BaseDaoImpl<Entity> implements BaseDao<Entity>{

    DBConn db = new DBConnJdbc();
    int r=0;
    private Class clazz;
    
    public BaseDaoImpl(){
        clazz = this.getClass();
        Type t = clazz.getGenericSuperclass();
        ParameterizedType pe = (ParameterizedType) t;
        clazz = (Class) pe.getActualTypeArguments()[0];
        System.out.println(clazz);
    }
    
    //添加
    @Override
    public int add(Entity obj) throws Exception {
        
        Connection con = db.getConn();
        String tables = clazz.getSimpleName();
        Field[] fields = clazz.getDeclaredFields();

        String sql = "insert into " + tables + " values(null";
        for (int i = 1; i < fields.length; i++) {
            sql += ",?";
        }
        sql = sql + ")";

        System.out.println(sql);
        PreparedStatement pstmt = null;
        pstmt = con.prepareStatement(sql);

        for (int i = 1; i < fields.length; i++) {
            String name= "get"+Character.toUpperCase(fields[i].getName().charAt(0));
            name = name + fields[i].getName().substring(1);
            Method m =clazz.getMethod(name);
            Object value= m.invoke(obj);        
            pstmt.setObject(i, value);
        }
        r=pstmt.executeUpdate();
        db.close(pstmt);
        db.close(con);
        return r;
        
    }

    //删除
    @Override
    public int delete(int id) throws Exception {
        Connection con = db.getConn();
        String tables = clazz.getSimpleName();
        Field[] fields = clazz.getDeclaredFields();
        
        String idName = fields[0].getName();
        
        String sql = "delete  from " + tables + " where "+idName+" ="+id;
        System.out.println(sql);
        PreparedStatement pstmt = null;
        pstmt = con.prepareStatement(sql);
        r=pstmt.executeUpdate();
        db.close(pstmt);
        db.close(con);
        return r;
        
    }

    //更新
    @Override
    public int update(Entity obj) throws Exception {
        Connection con = db.getConn();
        String tables = clazz.getSimpleName();
        String sql = "update "+tables+" set ";
        Field[] fields = clazz.getDeclaredFields();
        
        String idName = fields[0].getName();
        
        for(int i=1;i<fields.length;i++){
            sql += fields[i].getName() + "=?,";
        }
        sql = sql.substring(0,sql.length()-1)+" where "+idName+" = ?";
        System.out.println(sql);
        PreparedStatement pstmt = null;
        pstmt = con.prepareStatement(sql);
        for(int i =1;i<fields.length;i++){
            String methodName = "get"+Character.toUpperCase(fields[i].getName().charAt(0))+fields[i].getName().substring(1);
            Method m = clazz.getMethod(methodName);
            pstmt.setObject(i,m.invoke(obj));
        }
        
        String FiledFirstName = "get"+Character.toUpperCase(fields[0].getName().charAt(0))+fields[0].getName().substring(1);
        Method m2 = clazz.getMethod(FiledFirstName);
        pstmt.setInt(fields.length, (Integer)m2.invoke(obj));
        r=pstmt.executeUpdate();
        db.close(pstmt);
        return r;        
    }

    //根据id查询
    @Override
    public Entity get(int id) throws Exception {
        Connection con = db.getConn();
        
        Field[] fields = clazz.getDeclaredFields();
        String idName = fields[0].getName();
        
        String sql = "select * from "+clazz.getSimpleName() + " where "+idName+" =" +id;
        System.out.println(sql);
        PreparedStatement pstmt = null;
        pstmt = con.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        Entity entity =(Entity) clazz.newInstance();
        if(rs.next()){
            //Field[] fields = clazz.getDeclaredFields();
            for(int i=0;i<fields.length;i++){
                String methodName = "set" +Character.toUpperCase(fields[i].getName().charAt(0))+fields[i].getName().substring(1);
                Method m = clazz.getDeclaredMethod(methodName, fields[i].getType());
                Object value = rs.getObject(fields[i].getName());
                System.out.println(methodName + "=========" + value);
                m.invoke(entity, value);
            }
        }
        db.closeAll(rs, pstmt, con);
        return entity;
    }

    //全查
    @Override
    public List<Entity> getList() throws Exception {
        Connection con = db.getConn();
        String sql = "select * from "+clazz.getSimpleName();
        PreparedStatement pstmt = null;
        pstmt = con.prepareStatement(sql);
        List<Entity> list = new ArrayList<Entity>();
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()){
            Entity entrty = (Entity)clazz.newInstance();
            Field[] fields = clazz.getDeclaredFields();
            for(int i=0;i<fields.length;i++){
                String methodName = "set"+Character.toUpperCase(fields[i].getName().charAt(0))+fields[i].getName().substring(1);
                Method m = clazz.getMethod(methodName, fields[i].getType());
                m.invoke(entrty, rs.getObject(fields[i].getName()));
            }
            list.add(entrty);
        }
        System.out.println(sql);
        db.closeAll(rs, pstmt, con);
        return list;
    }

}

登录

package com.dao;

import com.entity.Person_info;

public interface Person_infoDao extends BaseDao<Person_info>{

    public boolean login(Person_info obj);
    public Person_info getLogin(String login_name);
}

package com.dao.impl;

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

import com.dao.Person_infoDao;
import com.db.DBConn;
import com.db.impl.DBConnJdbcProp;
import com.entity.Person_info;


public class Person_infoDaoImpl extends BaseDaoImpl<Person_info> implements Person_infoDao{
    
    //用户登录
    @Override
    public boolean login(Person_info obj) {
        
        boolean blean = false;
        String sql ="select *from person_info where login_name =? and password=?";
        DBConn db = new DBConnJdbcProp();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            con=db.getConn();
            pstmt=con.prepareStatement(sql);
            pstmt.setString(1,obj.getLogin_name());
            pstmt.setString(2,obj.getPassword());
            rs = pstmt.executeQuery();
            
            while(rs.next()){
                int user_id = rs.getInt("user_id");
                obj.setUser_id(user_id);;
                if(user_id>0){
                    blean = true;
                }        
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            db.close(con);
            db.close(pstmt);
            db.close(rs);
        }                
        return blean;
    }

    @Override
    public Person_info getLogin(String login_name) {
        
        String sql ="select *from person_info where login_name ='"+login_name+"'";
        DBConn db = new DBConnJdbcProp();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        Person_info pi = new Person_info();
        try {
            con = db.getConn();
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()){
                pi.setUser_id(rs.getInt("user_id"));
                pi.setLogin_name(rs.getString("login_name"));
                pi.setPassword(rs.getString("password"));
                pi.setPost_id(rs.getInt("post_id"));
                pi.setOther(rs.getString("other"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            db.closeAll(rs, pstmt, con);
        }
        return pi;
        
        
    }

}

登录Biz

package com.biz;

import java.util.List;

import com.entity.Person_info;

public interface Person_infoBiz {

    Person_info login(Person_info obj);
    int add(Person_info obj);
    int update(Person_info obj);
    Person_info getLogin(String login_name);
    
    Person_info get(int id);
    List<Person_info> getList();
    
}

package com.biz.impl;

import java.util.List;

import com.biz.Person_infoBiz;
import com.dao.Person_infoDao;
import com.dao.impl.Person_infoDaoImpl;
import com.entity.Person_info;

public class Person_infoBizImpl implements Person_infoBiz{

    Person_infoDao dao = new Person_infoDaoImpl();
    
    @Override
    public Person_info login(Person_info obj) {
        dao.login(obj);
        return obj;
    }

    @Override
    public int add(Person_info obj) {
        int rs = 0;
        try{
            rs=dao.add(obj);
        }catch(Exception e){
            rs = 0;
            e.printStackTrace();            
        }
        return rs;
    }

    @Override
    public int update(Person_info obj) {
        int rs = 0;
        try {
            rs=dao.update(obj);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;    
    }

    @Override
    public Person_info getLogin(String login_name) {
        
        return dao.getLogin(login_name);
    }

    @Override
    public Person_info get(int id) {
        
        Person_info pi = null;
        try {
            pi = dao.get(id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pi;
    }

    @Override
    public List<Person_info> getList() {
        List<Person_info> list = null;
        try {
            list = dao.getList();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}