数据库增删改查操作

今天写了一对数据库操作的小项目，那个bug调试的我是真的无语了
话不多说，直接上代码
总体的项目图

配置文件
jdbc.properties

driver =com.mysql.jdbc.Driver
connectionUrl=jdbc:mysql://localhost:port（端口）/DataName(数据库名)?useUnicode=true&characterEncoding=utf8
username =root
psw=****//自己的密码

commens 包下的工具类

jdbcUtil.java

package com.test.commons;
 
import java.sql.*;
import java.util.ResourceBundle;
 
public class jdbcUtil {
    //获取connection对象
    //关闭connection
    //关闭statement对象
    private static String driver;
    private static String connectionUrl;
    private static String username;
    private  static String psw;
 
    static {
        //读取propertile
        ResourceBundle bundle=ResourceBundle.getBundle("jdbc");
        driver=bundle.getString("driver");
        connectionUrl=bundle.getString("connectionUrl");
        username=bundle.getString("username");
        psw=bundle.getString("psw");
 
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        Connection conn=null;
        try {
            conn= DriverManager.getConnection(connectionUrl,username,psw);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static void rollback(Connection conn){
        try {
            if(conn!=null){
                conn.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void closeStatement(Statement state){
        if(state!=null){
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void closeConnection(Connection conn){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void closeResultSet(ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

randomUtil.java

package com.test.commons;
 
import java.util.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
 
/**
 * 随机生成三个字母组成名字
 */
public class randomUtil {
    public static String randomName(){
        char[] name=new char[3];
        String userName=" ";
        for(int i=0;i<3;i++){
            name[i]=(char)(Math.random()*26+97);
        }
        userName = String.valueOf(name);
        return userName;
    }
    /**
     *随机生成时间
     */
    public static Date randomTime(){
        Date time = null;
        StringBuilder sb= new StringBuilder();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int month = (int)(Math.random()*12+1);
        if(month==2) {//2018不是闰年
            sb.append("2018").append("-" + month).append("-" + (int)(Math.random() * 28+1)).
                    append(" " + (int)(Math.random() * 24)).append(":" + (int)(Math.random()*60)).
                    append(":" + (int)(Math.random()*60));
        }else if(month==1||month==3||month==5||month ==7||month==8||month==10|| month==12){//判断有31天的月份
            sb.append("2018").append("-" +month).append("-" + (int)(Math.random() * 31+1)).
                    append(" " + (int)(Math.random() * 24)).append(":" + (int)(Math.random() * 60)).
                    append(":" + (int)(Math.random() * 60));
        }else{//30天的月份
            sb.append("2018").append("-" + month).append("-" + (int)(Math.random() * 30+1)).
                    append(" " + (int)(Math.random() * 24)).append(":" + (int)(Math.random() * 60)).
                    append(":" + (int)(Math.random() * 60));
        }
        String randomTime=sb.toString();
        try {
           time = sdf.parse(randomTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return  time;
    }
    /**
     * 随机生成性别
     */
    public static String sex(){
        String sex="";
        if(Math.random()<=0.5){
            sex="男";
        }else{
            sex="女";
        }
        return sex;
    }
    /**
     * 生成分数
     */
    public static int score(){
        int score=(int)(Math.random()*201);
        return score;
    }
}

Dao数据访问层

baseDaoImpl.java

package Dao.impl;

import Dao.baseDao;
import com.test.commens.jdbcUtil;
import org.apache.commons.beanutils.BeanUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class baseDaoImpl implements baseDao {

    @Override
    public int executeUpdate(String sql, Object[] param) {
        Connection conn=null;
        PreparedStatement ps = null;
        int row=0;
        try {
            conn= jdbcUtil.getConnection();
            ps=conn.prepareStatement(sql);
            conn.setAutoCommit(false);手动提交
            //得到参数个数
            ParameterMetaData pmd = ps.getParameterMetaData();
            //绑定参数
            for(int i=0;i<pmd.getParameterCount();i++){
                ps.setObject(i+1,param[i]);
            }
            row=ps.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            jdbcUtil.rollback(conn);
        }finally{
            jdbcUtil.closeConnection(conn);
            jdbcUtil.closeStatement(ps);
        }

        return row;
    }

    @Override
    /**
     * 完成通用的查询方法
     * 通用的查询方法中要求模型对象的属性名必须和数据库表中的列名相同
     */

    public <T> List<T> Quary(String sql, Object[] param, Class<T> clazz) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> list = new ArrayList<>();
        try{
            conn = jdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            //得到参数个数
            ParameterMetaData pmd = ps.getParameterMetaData();
            //绑定参数
            for(int i=0;i<pmd.getParameterCount();i++){
                ps.setObject(i+1,param[i]);
            }
            //处理查询结果集
            rs = ps.executeQuery();
            //获取结果集的信息
            ResultSetMetaData rsmd = rs.getMetaData();
            while(rs.next()){
                //完成orm处理，通过jdk反射实现
                T t = clazz.newInstance();
                for(int j=0;j<rsmd.getColumnCount();j++){
                    //得到列名
                    String columnName = rsmd.getColumnName(j+1);
                    //获取列值
                    Object value = rs.getObject(columnName);
                    //时间类型转换
                    //对Date类型进行转换
                    if (columnName.equals("time")) {
                        Timestamp timestamp = (Timestamp)value;
                        long time = timestamp.getTime();
                        value = new java.util.Date(time);
                    }
                    //通过工具类将值放到对象中
                   BeanUtils.setProperty(t,columnName,value);

                }
                list.add(t);
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            jdbcUtil.closeConnection(conn);
            jdbcUtil.closeStatement(ps);
            jdbcUtil.closeResultSet(rs);
        }

        return list;
    }
}

userDaoImpl.java

package Dao.impl;

import Dao.baseDao;
import Dao.userDao;
import pojo.user;

import java.util.Date;
import java.util.List;

public class userDaoImpl extends baseDaoImpl implements userDao {
    @Override
    public int addUser(user u) {
        String sql="insert into user values (?,?,?,?,?)";
        Object[] param = new Object[]{u.getId(),u.getName(),u.getTime(),u.getScore(),u.getSex()};
        return this.executeUpdate(sql,param);
    }

    @Override
    public int dropUser(int id) {
        String sql = "delete  from user where id = ?";
        Object[] param = new Object[]{id};
        return this.executeUpdate(sql,param);
    }

    @Override
    public int updateUser(int id, int score) {
        String sql = "update user set score = ? where id = ?";
        Object[] param = new Object[]{score,id};
        return this.executeUpdate(sql,param);
    }

    @Override
    public List<user> findtUserByDate(int month) {
        String sql = "select * from user where date_format(time,'%Y-%c-%d %h:%i:%s') like ?";
        Object[] param = new Object[]{"%"+month+"%"};
        return this.Quary(sql,param,user.class);
    }

    @Override
    public List<user> findUserById(int id) {
        String sql = "select * from user where id = ?";
        Object[] param = new Object[]{id};
        return this.Quary(sql,param,user.class);
    }

    @Override
    public List<user> findtUserByScore(int score) {
        String sql = "select * from user where score > ?";
        Object[] param = new Object[]{score};
        return this.Quary(sql,param,user.class);
    }

}

baseDao.java

package Dao;

import java.util.List;

public interface baseDao {//增删改查
    public int executeUpdate(String sql,Object[] param);
    public <T> List<T> Quary(String sql, Object[] param, Class<T> clazz);
}

userDao.java

package Dao;

import pojo.user;

import java.util.Date;
import java.util.List;

public interface userDao {
    public int addUser(user u);
    public int dropUser(int id);
    public int updateUser(int id,int score);
    public List<user> findtUserByDate(int time);
    public List<user> findUserById(int id);
    public List<user> findtUserByScore(int score);
}

实体层

user.java

package pojo;

import java.util.Date;

public class user {
    private int id;
    private String name;
    private Date time;
    private int score;
    private String sex;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getTime() {
        return time;
    }

    public void setTime(Date time) {
        this.time = time;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }
}

服务层

userServiceImpl.java

package service.impl;

import Dao.impl.userDaoImpl;
import pojo.user;
import service.userService;

import java.util.Date;
import java.util.List;

public class userServiceImpl implements userService {
    @Override
    public void insertUser(user u) {
        userDaoImpl userdao = new userDaoImpl();
        userdao.addUser(u);
    }

    @Override
    public void deleteUser(int id) {
        userDaoImpl userdao = new userDaoImpl();
        userdao.dropUser(id);
    }

    @Override
    public void modifyUser(int id, int score) {
        userDaoImpl userdao = new userDaoImpl();
        userdao.updateUser(id,score);
    }

    @Override
    public List<user> selectUserByDate(int month) {
        userDaoImpl userdao = new userDaoImpl();
        return userdao.findtUserByDate(month);
    }

    @Override
    public List<user> selectUserById(int id) {
        userDaoImpl userdao = new userDaoImpl();
        return userdao.findUserById(id);
    }

    @Override
    public List<user> selectUserByScore(int score) {
        userDaoImpl userdao = new userDaoImpl();
        return userdao.findtUserByScore(score);
    }
}

userService.java

package service;

import pojo.user;

import java.util.Date;
import java.util.List;

public interface userService {
    public void insertUser(user u);
    public void deleteUser(int id);
    public void modifyUser(int id,int score);
    public  List<user> selectUserByDate(int month);
    public List<user> selectUserById(int id);
    public List<user> selectUserByScore(int score);
}

test存放测试类

operateData.java

package test;

import com.test.commens.randomUtil;
import pojo.user;
import service.impl.userServiceImpl;
import service.userService;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class operateData {
    public static void addData(){
        //添加数据
        List<user> list = new ArrayList<>();
        userService usi = new userServiceImpl();
        for(int i=0;i<100;i++){
            user u = new user();
            u.setId(100+i);
            u.setName(randomUtil.randomName());
            u.setTime(randomUtil.randomTime());
            u.setScore(randomUtil.score());
            u.setSex(randomUtil.sex());
            list.add(u);
        }
        for (user u:list) {
            usi.insertUser(u);
        }
        System.out.println("----------数据添加成功-----------");
    }
    public static void update(int id,int score){
        userService usi = new userServiceImpl();
        usi.modifyUser(id,score);
        System.out.println("----------修改数据成功-----------");
    }
    public static void delete(int id){
        userService usi = new userServiceImpl();
        usi.deleteUser(id);
        System.out.println("----------删除数据成功-----------");
    }
    public static void quaryDate(int month){
        userService usi = new userServiceImpl();
        List<user> list=usi.selectUserByDate(month);
        for(user u:list){
            System.out.println(u.getId()+" "+u.getName()+" "+u.getTime()+" "+u.getScore()+" "+u.getSex());
        }
    }
    public static void quaryId(int id){
        userService usi = new userServiceImpl();
        List<user> list = usi.selectUserById(id);
        for(user u:list){
            System.out.println(u.getId()+" "+u.getName()+" "+u.getTime()+" "+u.getScore()+" "+u.getSex());
        }
    }
    public static void quary_higher_Score(int score){
        userService usi = new userServiceImpl();
        List<user> list = usi.selectUserByScore(score);
        for(user u:list){
            System.out.println(u.getId()+" "+u.getName()+" "+u.getTime()+" "+u.getScore()+" "+u.getSex());
        }
    }
}

test.java

package test;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;

public class test {
    public static void main(String[] args) throws ParseException {
        while (true) {
            menu();
            Scanner sc = new Scanner(System.in);
            int sel = sc.nextInt();
            switch (sel) {
                case 0:
                    return;
                case 1:
                    operateData.addData();
                    break;
                case 2:
                    int id = sc.nextInt();
                    int score = sc.nextInt();
                    operateData.update(id, score);
                   break;
                case 3:
                    int u_id = sc.nextInt();
                    operateData.delete(u_id);
                    break;
                case 4:
                    int month=sc.nextInt();
                    operateData.quaryDate(month);
                    break;
                case 5:
                    int u_id1 = sc.nextInt();
                    operateData.quaryId(u_id1);
                    break;
                case 6:
                    int point = sc.nextInt();
                    operateData.quary_higher_Score(point);
                    break;
                default:
                    break;
            }

        }
    }
    public static void menu(){
        System.out.println("请输入数字(0-6):");
        System.out.println("1. 批量添加100个用户");
        System.out.println("2. 根据用户ID修改积分");
        System.out.println("3. 根据用户ID删除用户");
        System.out.println("4. 根据注册月份查询用户信息");
        System.out.println("5. 根据用户ID查询用户信息");
        System.out.println("6. 根据积分大于某个值查询用户信息");
        System.out.println("0. 退出");
    }
}

运行如下









用到了三个插件，这个自己去下载