[大数据]数据库操作工具（CRUD）

package connectionutil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionUtil {
    private static String URL="jdbc:mysql://localhost:3307/scott"+"?useSSL=false&useUnicode=true"+"&characterEncoding=UTF-8&serverTimezone=UTC";
    //	private static String URL = "jdbc:mysql://localhost:3307/scott" +"?useUnicode=true&useJDBCCompliantTimezoneShift=true"+"&useLegacyDatetimeCode=false&serverTimezone=UTC";
    private static String DRIVER="com.mysql.cj.jdbc.Driver";
    private static String USER = "biubiu";
    private static String PASSWORD = "123456";
    //加载驱动
    static{
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    /**
     * 取得连接对象
     * @return
     */
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
    public static void close(Connection conn){
        try {
            conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}

package connectionutil;
import java.io.Serializable;
import java.util.Date;

public class Emp implements Serializable {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Double sal;
    private Date hiredate;
    private Double comm;
    private Integer deptno;

    public Emp() {
    }

    public Emp(Integer empno, String ename, String job, Integer mgr, Double sal, Date hiredate, Double comm, Integer deptno) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.sal = sal;
        this.hiredate = hiredate;
        this.comm = comm;
        this.deptno = deptno;
    }

    public Integer getEmpno() {
        return empno;
    }

    public void setEmpno(Integer empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public Integer getMgr() {
        return mgr;
    }

    public void setMgr(Integer mgr) {
        this.mgr = mgr;
    }

    public Double getSal() {
        return sal;
    }

    public void setSal(Double sal) {
        this.sal = sal;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public Double getComm() {
        return comm;
    }

    public void setComm(Double comm) {
        this.comm = comm;
    }

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", sal=" + sal +
                ", hiredate=" + hiredate +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }
}

package connectionutil.demo;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
public class Dbuttil {
    private static PreparedStatement pst;
    private static ResultSet rst;

    /**
     * 实现删除满足条件的的数据
     * @param conn
     * @param sql
     * @param params
     * @return
     * @throws Exception
     */
    public static int remove(Connection conn,String sql,Object...params) throws Exception{
        pst = conn.prepareStatement(sql);
        for (int i =0;i<params.length;i++){
            pst.setObject(i+1,params[i]);
        }
        return pst.executeUpdate();
    }

    /**
     * 实现批量删除数据
     * @param conn
     * @param sql
     * @param ids
     * @return
     * @throws Exception
     */
    public static int remove(Connection conn, StringBuffer sql, Set<Object> ids)throws Exception{
        Iterator<Object> iter = ids.iterator();
        while (iter.hasNext()){
            sql.append(iter.next()+",");
        }
        sql.delete(sql.length()-1,sql.length());
        sql.append(")");
        pst = conn.prepareStatement(sql.toString());//必须是String类型
        //执行语句
        return pst.executeUpdate();
    }

    /**
     * 根据条件查询单个数据
     * 大致思路：1、从数据库中读取满足要求的数据
     * 2、将读取的结果，赋值给传入的对象（利用反射，取得对象的set方法）
     * @param conn
     * @param sql
     * @param clz
     * @param params
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> T selectOne(Connection conn,String sql,Class<T> clz,Object...params) throws Exception{
        pst = conn.prepareStatement(sql);
        for (int i=0;i<params.length;i++){
            pst.setObject(i+1,params[i]);
        }
        //执行语句
        ResultSet rst = pst.executeQuery();
        T t = null;
        if(rst.next()){
            t = clz.newInstance();//实例化对象
            //取得类的所有属性
            Field[] fs = clz.getDeclaredFields();
            //遍历所有的属性
            for(int i =0;i<fs.length;i++){
                //取得属性名称
                String fname = fs[i].getName();
                //取得属性对应的方法名
                Method m = clz.getDeclaredMethod("set"+uppercase(fname),fs[i].getType());//区别重载的方法
                //根据属性名从结果中取得对应的值
                Object fvalue = null;
                try {
                    fvalue = rst.getObject(fname);
                }catch (Exception e){
                    e.printStackTrace();
                }
                m.invoke(t,fvalue);

            }
        }
        return t;
    }

    /**
     * 根据条件查询数据
     * 大致思路，从数据库中按照要求读取数据，每读取一条数据就赋值给传入的对象，并将对象保存到集合中
     *
     * @param conn
     * @param sql
     * @param clz
     * @param params
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> selectList(Connection conn,String sql,Class<T> clz,Object...params) throws Exception{
        pst = conn.prepareStatement(sql);
        List<T> listObjs = new ArrayList<>();
        for(int i = 0;i<params.length;i++){
            pst.setObject(i+1,params[i]);
        }
        //执行语句
        ResultSet rst = pst.executeQuery();
        T t = null;
        while (rst.next()){
            t = clz.newInstance();
            //取得所有顺序性
            Field [] fs=clz.getDeclaredFields();
            for (int i =0;i<fs.length;i++){
                String fname = fs[i].getName();
                Method m = clz.getDeclaredMethod("set"+uppercase(fname),fs[i].getType());
                //根据属性名从结果集中取得对应的值
                Object fvalue = null;
                try {
                    fvalue = rst.getObject(fname);
                }catch (Exception e){
                    e.printStackTrace();
                }
                //将数据保存到传入的对象中
                m.invoke(t,fvalue)
            }
            listObjs.add(t);

        }
        return listObjs;
    }
    public static int selectCount(Connection conn,String sql,Object...params) throws Exception{
        pst = conn.prepareStatement(sql);
        for(int i = 0;i<params.length;i++){
            pst.setObject(i+1,params[i]);
        }
        ResultSet rst = pst.executeQuery();
        if(rst.next()){
            return rst.getInt(1);
        }
        return 0;
    }


    /**
     * 保存数据
     * @param conn
     * @param sql
     * INSERT INTO emp(ename,job,sal,hiredate,mgr,comm,deptno) VALUES(?,?,?,?,?,?,?);
     * @param vo保存了要增加的数据的VO对象
     * @param getAutokey
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> int save(Connection conn, String sql, T vo, boolean getAutokey) throws Exception {
        pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        //为sql中的占位符赋值
        //INSERT INTO emp(ename,job,sal,hiredate,mgr,comm,deptno) VALUES(?,?,?,?,?,?,?);
        //取得列字段的名称，并保存在数组中
        String [] columns = sql.split("\\(")[1].split("\\)")[0].split(",");
        //利用放着将vo对象中的属性值取出来
        Class<?> cls = vo.getClass();
        for(int i = 0;i<columns.length;i++){
            String column = columns[i];
            //取得相应的get方法并获取数据
            Method m = cls.getDeclaredMethod("get"+uppercase(column));
            //m.setAccessible(true);
            Object obj = m.invoke(vo);
            //把获取的Object赋值给占位符
            pst.setObject(i+1,obj);
        }
        //执行插入数据的sql语句
        if(getAutokey){
            rst = pst.getGeneratedKeys();
            while (rst.next()){
                return rst.getInt(1);
            }
        }else {
            return pst.executeUpdate();
        }
        return 0;

    }
    /**
     * 更新数据
     * @param conn
     * @param sql：UPDATE emp set ename=?,job=?,sal=? WHERE empno = 1000 AND sal>1000;
     * @param vo
     * @param <T>
     * @return
     * @throws Exception
     */
    public static<T>  int edit(Connection conn,String sql,T vo) throws Exception{
        //对sql语句进行预编译
        pst = conn.prepareStatement(sql);
        Class cls = vo.getClass();
        //UPDATE emp set ename=?,job=?,sal=? WHERE empno = ?;
        String [] columns = sql.split("WHERE")[0].split("SET")[1].trim().split(",");
        //[ename=? job=? sal=?]
        //遍历字符串数组
        for (int i = 0;i<columns.length;i++){
            //取得字段
            String column = columns[i].split("=")[0].trim();
            //取得对象类中的对应的方法
            Method m = cls.getDeclaredMethod("get"+uppercase(column));
            //取得值
            Object obj = m.invoke(vo);
            //设置给占位符
            pst.setObject(i+1,obj);
        }
        //UPDATE emp set ename=?,job=?,sal=? WHERE empno = ? ;
        //条件查询
        String  conditions = sql.split("WHERE")[1].split("=")[0].trim();
        Object obj = cls.getDeclaredMethod("get"+uppercase(conditions)).invoke(vo);
        pst.setObject(columns.length+1,obj);
        //执行SQL
        return pst.executeUpdate();
    }
    public static<T>  int edit(Connection conn,String sql,Object...params) throws Exception{
        //对sql语句进行预编译
        pst = conn.prepareStatement(sql);
        //UPDATE emp set ename=?,job=?,sal=? WHERE empno = ?;
        for (int i = 0;i<params.length;i++){
            pst.setObject(i+1,params[i]);
        }
        return pst.executeUpdate();
    }

    public  static String uppercase(String column){
        return  column.substring(0,1).toUpperCase()+column.substring(1);
    }
}

package connectionutil;
import connectionutil.demo.Dbuttil;
import org.omg.CORBA.OBJECT_NOT_EXIST;

import java.sql.*;
import java.util.Date;
import java.util.Iterator;
import java.util.Set;

public class MysqlTest {
    public static void main(String[] args) throws Exception {
        //Emp emp = new Emp(1002,"xxxx","xxxx",7788,1000.0,new Date(),1000.0,10);
        //Emp emp = new Emp(7904,"yyy","yyy",7788,1000.0,new Date(),1000.0,10);
        System.out.println(update("jack","president",10000.0,7904));
    }
    /**
     * 根据empno查询数据
     *
     */
    public  static  void getEmpByid(Integer id) throws SQLException {
        //取得连接对象
        Connection conn = ConnectionUtil.getConnection();
        try {
            //准备sql语句
            String sql = "SELECT empno,ename,job,hiredate,sal FROM emp WHERE empno ="+id;
            //转换指令
            PreparedStatement pst = conn.prepareStatement(sql);
            //执行语句
            ResultSet rst = pst.executeQuery();
            while (rst.next()){
                System.out.println("雇员编号："+rst.getObject("empno")+",雇员姓名"+rst.getObject("ename")+",入职日期"+
                        rst.getObject("hiredate")+",薪资"+rst.getObject("sal"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //关闭连接
            ConnectionUtil.close(conn);
        }

    }
    /**
     * 实现模糊分页查询
     * 模糊ename字符串kw
     * 参数：当前页cp
     * 每页显示的数据ls条
     */
    public static  void getEmpData(String kw,Integer cp,Integer ls) throws SQLException {
        if (kw==null){
            kw = "";
        }
        kw="'%"+kw+"%'";
        //处理页数和数据量
        //获取当前页第一行数据的索引。数据的索引从0开始
        //获取连接对象
        Connection conn = ConnectionUtil.getConnection();
        try {
            //准备sql语句
            String sql = "SELECT * FROM emp WHERE ename LIKE "+kw+" limit "+(cp-1)*ls+","+ls;//注意关键字之间的空格
            //指令处理
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rst = pst.executeQuery();
            while (rst.next()){
                System.out.println("雇员编号："+rst.getObject("empno")+",雇员姓名"+rst.getObject("ename")+",入职日期"+
                        rst.getObject("hiredate")+",薪资"+rst.getObject("sal"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectionUtil.close(conn);
        }
    }

    /**
     * 同时删除多条数据
     * @param num
     * @throws Exception
     */
    public static boolean deletemp(Set<Integer> ids)throws Exception{
        //取得连接对象
        Connection conn = ConnectionUtil.getConnection();
        //准备将要执行的SQL语句
        StringBuffer sql = new StringBuffer("DELETE FROM emp where empno in(");
        Iterator<Integer> iterator = ids.iterator();
        while (iterator.hasNext()){
            Integer id = iterator.next();
            sql.append(id+",");
        }
        //最后一个empno后有一个逗号，且在语句后面加一个）
        sql.delete(sql.length()-1,sql.length());//删除最后一个逗号
        sql.append(")");
        //取得预编译的对象
        PreparedStatement pst=conn.prepareStatement(sql.toString());
        //执行语句
        int row = pst.executeUpdate();
        //关闭连接
        ConnectionUtil.close(conn);
        return row>0;
    }

    /**
     * 更新数据
     * @param conn
     * @param sql：UPDATE emp set ename=?,job=?,sal=? WHERE empno = 1000 AND sal>1000;
     * @param vo
     * @param <T>
     * @return
     * @throws Exception
     */
    public static boolean update(Emp emp) throws Exception{
        //取得连接对象
        Connection conn = ConnectionUtil.getConnection();
        //准备sql语句
        String sql = "UPDATE emp SET ename = ?,job = ?,sal = ? WHERE empno = ?";
        return Dbuttil.edit(conn,sql,emp)>0;
    }
    public static boolean update(Object...params) throws Exception{
        //取得连接对象
        Connection conn = ConnectionUtil.getConnection();
        //准备sql语句
        String sql = "UPDATE emp SET ename = ?,job = ?,sal = ? WHERE empno = ?";
        return Dbuttil.edit(conn,sql,params)>0;
    }


    /**
     * 插入数据并获取自动增长主键
     * @return
     * @throws Exception
     */
    public static boolean insertEmp(Emp emp) throws Exception{
        //取得连接对象
        Connection conn = ConnectionUtil.getConnection();
        //定义语句
        //String sql = "INSERT INTO emp(ename,job,sal,hiredate,mgr,comm,deptno) VALUES(?,?,?,?,?,?,?)";
        String sql = "INSERT INTO emp(ename,job,sal) VALUES(?,?,?)";
        return Dbuttil.save(conn,sql,emp,false)>0;
    }

}