封装数据库工具类（增删改查、日期类）

DAO

---

DAO（Data Access Object）数据访问对象：将所有对同一张表的操作都封装在一个XXXDaoImpl对象中、根据增删改查的不同功能，实现具体的方法(insert,update,delete,select,selectAll)；

• 对于任何一张表中的数据进行操作时（增、删、改、查）；应将对于一张表的所有操作统一封装在一个数据访问对象中为了实现重用；

CREATE TABLE `user`(
	id INT PRIMARY KEY,#主键
	username VARCHAR(20) NOT NULL,
	`password` VARCHAR(20) NOT NULL,#非空
	sex CHAR(2),
	email VARCHAR(50) NOT NULL,
	address VARCHAR(20) NOT NULL
)CHARSET = utf8;

/**
 * 对数据库中User表的一系列操作(增删改查)
 * 只做实现访问操作
 * 对同一张表的操作
 */
import com.kaka.test_02.DBUtils;
import com.kaka.test_02.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDaoImpl {
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    //增
    public int insert(User user){
        try{
            connection = DBUtils.getConnection();
            String sql = "insert into user(id,username,password,sex,email,address) values(?,?,?,?,?,?);";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,user.getId());
            preparedStatement.setString(2,user.getUsername());
            preparedStatement.setString(3,user.getPassword());
            preparedStatement.setString(4,user.getSex());
            preparedStatement.setString(5,user.getEmail());
            preparedStatement.setString(6,user.getAddress());
            int i = preparedStatement.executeUpdate();
            //将操作结果返回给调用者
            return i;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }
    //删
    public int delete(int id){
        connection = DBUtils.getConnection();
        String sql = "delete from user where id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return 0;
    }
    //改
    public int update(User user){
        connection = DBUtils.getConnection();
        String sql = "update user set username=?,password=?,sex=?,email =?,address=? where id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getUsername());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.setString(3,user.getSex());
            preparedStatement.setString(4,user.getEmail());
            preparedStatement.setString(5,user.getAddress());
            preparedStatement.setInt(6,user.getId());
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return 0;
    }
    //查单个
    public User select(int id){
        connection = DBUtils.getConnection();
        String sql = "select id,username,password,sex,email,address from user where id = ?;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id1 = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String sex = resultSet.getString("sex");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");
                User user = new User(id,username,password,sex,email,address);
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return null;
    }
    //查多个
    public List<User> selectAll(){
        connection = DBUtils.getConnection();
        String sql = "select id,username,password,sex,email,address from user";
        List<User> userList = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String sex = resultSet.getString("sex");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");
                User user = new User(id, username, password, sex, email, address);
               //每封装完一个对象，添加到集合当中
                userList.add(user);
            }
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return null;
    }
}

/**
 * 测试类
 */

import com.kaka.test_02.User;
import java.util.List;
public class TestDao {
    public static void main(String[] args) {
        //新增
        UserDaoImpl udi = new UserDaoImpl();
        User user = new User(2,"张三","123456","男","123456.com","陕西");
//        int result = udi.insert(user);
//        if(result > 0){
//            System.out.println("新增成功！");
//        }else{
//            System.out.println("新增失败！");
//        }
        
        
//        删除
//        int result = udi.delete(2);
//        System.out.println(result);
        
        
        //修改
//        User users = new User(1001,"卡卡","1234","男","12345678910@qq.com","shanxi");
//        int result = udi.update(users);
//        System.out.println(result);
        
        
        //查单个
        User k = udi.select(1001);
        System.out.println(k);
        
        
        //查多个
        List<User> userList = udi.selectAll();
        userList.forEach(System.out::println);
    }
}

日期类型

---

• java.util.Date

  Java语言常规应用层面的日期类型；可以通过字符串创建对应的时间对象；无法直接通过JDBC插入数据库

• java.sql.Date

  不可以通过字符串创建对应的时间对象；只能通过毫秒值创建对象；可以直接通过JDBC插入数据库

sql赋util可以，util赋sql不行，因为sql是util的子类；

日期格式化工具

• SimpleDateFormat 日期格式化

//自定义一个时间
        String str = "2020-02-02";
        //日期转换将字符串转为 java.util.Date
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //将日期字符串转换成 util.Date类型
        java.util.Date utilDate  = sdf.parse(str);
        System.out.println(utilDate);

日期工具类

/**
 * 日期转换
 * 字符串转UtilDate
 * 字符串转SqlDate
 * utilDate转成SqlDate
 */
import java.text.ParseException;
import java.text.SimpleDateFormat;
public class DateUtils {
    private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    //字符串转Util
    public static java.util.Date strToUtilDate(String str) {
        try {
            return simpleDateFormat.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
    //字符串转sql(不常用)
    public static java.sql.Date strToSqlDate(String str){
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            java.util.Date date = simpleDateFormat.parse(str);
            return new java.sql.Date(date.getTime());
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }

    //util转sql
    public static java.sql.Date utilToSql(java.util.Date date){
        return new java.sql.Date(date.getTime());
    }
}

测试类

  public static void main(String[] args) throws ParseException{
        //1.java.util.Date
        // 当前系统时间
        System.out.println(new java.util.Date());

        //自定义一个时间
        String str = "2020-02-02";
        //日期转换将字符串转为 java.util.Date
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //将日期字符串转换成 util.Date类型
        java.util.Date utilDate  = sdf.parse(str);
        System.out.println(utilDate);

        //sql.Date  需要毫秒值，来构建一个日期
        java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
        System.out.println(sqlDate);

        java.util.Date date = DateUtils.strToUtilDate("2020-03-28");
        System.out.println(date);

        java.sql.Date date2 = DateUtils.utilToSql(date);
        System.out.println(date2);
    }
}