使用JDBC完成数据的增删改查

实体类：

package com.study.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int id;
    private String username;
    private String password;
    private String email;
    private Date birthday;
}

创建工具类：

package com.study.Utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
    //静态代码块,在类加载的时候执行
    static{
        init();
    }
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    //初始化连接参数,从配置文件里获得
    public static void init(){
        Properties params=new Properties();
        String configFile = "db.properties";
        InputStream is= JDBCUtils.class.getClassLoader().getResourceAsStream(configFile);
        try {
            params.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println();
        driver=params.getProperty("driver");
        url=params.getProperty("url");
        user=params.getProperty("username");
        password=params.getProperty("password");
    }
    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnection(){
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return connection;
    }
    /**
     * 关闭数据库连接,释放资源
     * @return
     */
    public static boolean closeResource(Connection connection, Statement statement, ResultSet resultSet){
        boolean flag = true;
        if (resultSet!=null){
            try {
                resultSet.close();
                resultSet = null;//如果释放失败，就GC回收
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }
        if (statement!=null){
            try {
                statement.close();
                statement = null;//如果释放失败，就GC回收
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }
        if (connection!=null){
            try {
                connection.close();
                connection = null;//如果释放失败，就GC回收
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }
        return flag;
    }
    /**
     * 关闭数据库连接,释放资源
     * @return
     */
    public static boolean close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        boolean flag = true;
        if (resultSet!=null){
            try {
                resultSet.close();
                resultSet = null;//如果释放失败，就GC回收
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }
        if (preparedStatement!=null){
            try {
                preparedStatement.close();
                preparedStatement = null;//如果释放失败，就GC回收
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }
        if (connection!=null){
            try {
                connection.close();
                connection = null;//如果释放失败，就GC回收
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }
        return flag;
    }
}

创建Dao：

package com.study.dao;
import com.study.Utils.JDBCUtils;
import com.study.pojo.User;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
public class UserDao {
    //插入一条数据方法
    public boolean insert(User user){
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            //获得数据的连接            //获得Statement对象
            connection = JDBCUtils.getConnection();
            statement= connection.createStatement();
           //发送Sql语句
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = simpleDateFormat.format(user.getBirthday());
            String sql="INSERT INTO users(id,NAME,PASSWORD,email,birthday)" +
                    " VALUES('" +
                     user.getId()
                    + "','"
                    +user.getUsername()
                    + "','"
                    +user.getPassword()
                    + "','"
                    +user.getEmail()
                    + "','"
                    + birthday + "')";
            int num=statement.executeUpdate(sql);
            if (num>0){
                return true;
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtils.closeResource(connection,statement,resultSet);
        }
        return false;
    }
    //查询所有的的User对象
    public ArrayList<User> findALl(){
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        ArrayList<User> list=new ArrayList<User>();

        try {
            //获取连接
            connection = JDBCUtils.getConnection();
            statement  = connection.createStatement();
            String sql="select * from users";
            resultSet= statement.executeQuery(sql);
            while (resultSet.next()){
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setEmail(resultSet.getString("email"));
                user.setBirthday(resultSet.getDate("birthday"));
                list.add(user);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(connection,statement,resultSet);
        }
        return null;
    }
    //根据id查找指定的user
    public User find(int id){
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            //获取连接
            connection= JDBCUtils.getConnection();
            //获得 statement
            statement= connection.createStatement();
            //编写 sql语句
            String sql="select * from users where id="+id;
            resultSet=statement.executeQuery(sql);
            //处理结果集
            while (resultSet.next()){
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setEmail(resultSet.getString("email"));
                user.setBirthday(resultSet.getDate("birthday"));
               return user;
            }
            return null;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(connection,statement,resultSet);
        }
        return null;
    }
    //删除用户
    public boolean delete(int id){
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection= JDBCUtils.getConnection();
            statement=  connection.createStatement();
            String sql="delete from users where id="+id;
            int num=statement.executeUpdate(sql);
            if(num>0){
                return true;
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(connection,statement,resultSet);
        }
        return false;
    }
    //修改用户
    public boolean update(User user){
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection= JDBCUtils.getConnection();
            statement=connection.createStatement();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String birthday=simpleDateFormat.format(user.getBirthday());
            String sql="update users set name='"+user.getUsername()
                    + "',password='"+user.getPassword()+"',email='"+user.getEmail()+"',birthday='"+birthday
                    + "' where id="+user.getId();
            int num=statement.executeUpdate(sql);
            if (num>0){
                return true;
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(connection,statement,resultSet);
        }
        return false;
    }
}

测试：

import com.study.dao.UserDao;
import com.study.dao.UserDaoPstm;
import com.study.pojo.User;
import org.junit.Test;

import java.util.Date;

public class JdbcInsertTest {
    public static void main(String[] args) {
        //向 users 表中插入员工用户信息
        UserDao userDao = new UserDao();
        User user = new User();
        user.setId(7);
        user.setUsername("ooooo");
        user.setPassword("88888");
        user.setEmail("qq.com");
        user.setBirthday(new Date());
        boolean insert = userDao.insert(user);
        System.out.println(insert);
    }
    @Test
    //查询所有数据测试
    public void findAll(){
        UserDao userDao = new UserDao();
        for (User user : userDao.findALl()) {
            System.out.println(user);
        }
    }
    @Test
    //通过id查询数据测试
    public void find(){
        UserDao userDao = new UserDao();
        User user = userDao.find(3);
        System.out.println(user);
    }
    @Test
    //通过id删除数据测试
    public  void delete(){
        UserDao userDao = new UserDao();
        boolean delete = userDao.delete(7);
        System.out.println(delete);
    }
    @Test
    //通过 id 修改数据测试
    public void update(){
        UserDao userDao = new UserDao();
        boolean flag = userDao.update(new User(6, "oys", "99999", "USer0994@163.com", new Date()));
        System.out.println(flag);
    }
    @Test
    //插入数据
    public void insert(){
        UserDaoPstm userDaoPstm = new UserDaoPstm();
        boolean oys = userDaoPstm.insert(new User(8, "oys", "99999", "USer0994@163.com", new Date()));
        System.out.println(oys);

    }
}