Demo_JDBC_CRUD(在MyEcilpse中对MySQL的数据进行增删改查)

需求：通过Java代码增删改查MySQL数据库中的数据

1、新建一个Java Project

2、新建一个User类

package com.soar.entity;

import java.util.Date;

public class User {
    private int id;
    private String name;
    private String password;
    private String email;
    private Date birthday;

    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 String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", password=" + password + ", email=" + email + ", birthday="
                + birthday + "]";
    }


}

3、新建一个properties的配置文件，在配置文件中添加相应的数据

driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql:///mydb
username = root
password =root

注意：不需要加分号

4、新建一个DBUtils的类

package com.soarutil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ResourceBundle;

public class DBUtils {
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;

    static{
        //此对象用于加载properties文件数据的
        ResourceBundle rb = ResourceBundle.getBundle("dbinfo");
        driverClass = rb.getString("driverClass");
        url = rb.getString("url");
        username = rb.getString("username");
        password = rb.getString("password");

        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {

            e.printStackTrace();
        }
    }

    //得到连接的方法
    public static Connection getConnection() throws Exception{
        return DriverManager.getConnection(url,username,password);
    }

    //关闭资源的方法
    public static void closeALL(ResultSet rs, Statement stmt, Connection conn){
        //关闭资源
        if(rs!=null){
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

5、新建一个TestCRUD的类

package com.soarutil;

import java.awt.List;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.junit.Test;

import com.mysql.jdbc.PreparedStatement;
import com.soar.entity.User;

public class TestCRUD {
    //此方法用来查询数据
    @Test
    public void testSelect(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtils.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from users");
            ArrayList<User> list = new ArrayList<User>();
            while(rs.next()){
                User u = new User();
                u.setId(rs.getInt(1));
                u.setName(rs.getString(2));
                u.setPassword(rs.getString(3));
                u.setEmail(rs.getString(4));
                u.setBirthday(rs.getDate(5));
                list.add(u);
            }

            for (User user : list) {
                System.out.println(user);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            DBUtils.closeALL(rs, stmt, conn);
        }
    }

    //此方法用来插入数据
    @Test
    public void testInsert(){
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtils.getConnection();

            stmt = (PreparedStatement) conn.prepareStatement("INSERT INTO users VALUES(?,?,?,?,?)");    
            stmt.setInt(1, 4);
            stmt.setString(2,"tom");
            stmt.setString(3, "333");
            stmt.setString(4, "tom@163.com");
            stmt.setDate(5, new java.sql.Date(System.currentTimeMillis()));
            int i = stmt.executeUpdate();

            if(i>0){
                System.out.println("success");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            DBUtils.closeALL(null, stmt, conn);
        }
    }

    //此方法用来更新数据
    @Test
    public void testUpdate(){
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtils.getConnection();
            stmt = (PreparedStatement) conn.prepareStatement("UPDATE users SET NAME=?,PASSWORD=?,email=? where id=?");  
            stmt.setString(1, "jerry123");
            stmt.setString(2, "123");
            stmt.setString(3, "jerry123@163.com");
            stmt.setInt(4, 5);
            int i = stmt.executeUpdate();
            if(i>0){
                System.out.println("success");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            DBUtils.closeALL(null, stmt, conn);
        }
    }

    //此方法用来删除数据
    @Test
    public void testDelete(){
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = DBUtils.getConnection();
            stmt = conn.createStatement();  
            int i = stmt.executeUpdate("DELETE FROM users WHERE id=4");
            if(i>0){
                System.out.println("success");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            DBUtils.closeALL(null, stmt, conn);
        }
    }
}

所有用到的类以及文件如图：