JDBC-简单的增删改查完整代码

最新推荐文章于 2023-07-06 23:32:14 发布

池武

最新推荐文章于 2023-07-06 23:32:14 发布

阅读量261

点赞数 1

文章标签： jar java 开发语言 jdbc

本文链接：https://blog.csdn.net/m0_63144452/article/details/120946165

版权

该博客展示了如何使用Java JDBC连接并操作MySQL数据库，包括读取、插入、更新和删除用户信息。通过BaseDao父类实现数据库连接的通用方法，并在UserDao子类中实现具体的数据操作。测试类验证了各个操作的正确性。

摘要由CSDN通过智能技术生成

需要导入---->junit-4.10.jar

----->mysql-connector-java-8.0.20.jar

----->db.properties代码

username=root
password=123456
driverName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_cs?serverTimezone=Asia/Shanghai

------>User实体类

public class User {
    private Integer uid;
    private String uname;
    private String sex;
    private String password;
    public Integer getUid() {
        return uid;
    }
    public void setUid(Integer uid) {
        this.uid = uid;
    }
    public String getUname() {
        return uname;
    }
    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getSex() {
        return sex;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

}

------>BaseDao父类

package Test.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class BaseDao {
    public Connection connection;
    public PreparedStatement ps;
    public ResultSet resultSet;
    private static String username;
    private static String password;
    private static String driverName;
    private static String url;

    static{
        try{
            InputStream resourceAsStream = BaseDao.class.getResourceAsStream("/db.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);

            username = properties.get("username").toString();
            password = properties.get("password").toString();
            driverName = properties.get("driverName").toString();
            url = properties.get("url").toString();
            Class.forName(driverName);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void getConnection() throws Exception{
        connection = DriverManager.getConnection(url,username,password);
    }

    public int update(String sql,Object... params){
        try{
            getConnection();
            ps = connection.prepareStatement(sql);
            for(int index=0;index<params.length;index++){
                ps.setObject(index+1,params[index]);
            }
            int i = ps.executeUpdate();
            return i;

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            closeAll();
        }
        return 0;
    }

    public void closeAll(){
        try{
            if(resultSet != null){
                resultSet.close();
            }
            if(ps != null){
                ps.close();
            }
            if(connection != null){
                connection.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }



}

------>子类UserDao

package Test.dao;

import Test.entity.User;
import Test.util.BaseDao;

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

public class UserDao extends BaseDao {
    //查询所有
    public List<User> findAll(){
        List<User> list = new ArrayList<>();
        try{
            getConnection();
            String sql = "select * from cs";
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();

            while(resultSet.next()){
                User user = new User();
                user.setUid(resultSet.getInt("uid"));
                user.setUname(resultSet.getString("uname"));
                user.setSex(resultSet.getString("sex"));
                user.setPassword(resultSet.getString("password"));
                list.add(user);
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally{
            closeAll();
        }
        return list;
    }

    //根据id查询
    public User findById(Integer id){
        User user = null;
        try{
            getConnection();
            String sql = "select * from cs where uid=?";
            ps = connection.prepareStatement(sql);
            ps.setObject(1,id);
            resultSet = ps.executeQuery();

            while(resultSet.next()){
                user = new User();
                user.setUid(resultSet.getInt("uid"));
                user.setUname(resultSet.getString("uname"));
                user.setSex(resultSet.getString("sex"));
                user.setPassword(resultSet.getString("password"));

            }

        }catch (Exception e){
            e.printStackTrace();
        }finally{
            closeAll();
        }
        return user;
    }

    //删除
    public int deleteById(int id){
        String sql = "delete from cs where uid=?";
        return update(sql,id);
    }

    //修改
    public int update(User user){
        String sql = "update cs set uname=?,sex=?,password=? where uid=?";
        int row = update(sql,user.getUname(),user.getSex(),user.getPassword(),user.getUid());
        return row;
    }

    //增加
    public int insert(User user){
        String sql = "insert into cs(uid,uname,sex,password) values(null,?,?,?)";
        int row = update(sql,user.getUname(),user.getSex(),user.getPassword());
        return row;
    }












}

----->测试类

package Test.test;

import Test.dao.UserDao;
import Test.entity.User;
import org.junit.Test;

import java.util.List;

public class TestJDBC {
    UserDao userDao = new UserDao();

    @Test
    public void testfindAll(){
        List<User> list = userDao.findAll();
        for(User u:list){
            System.out.println(u.getUid()+"\t"+u.getUname());
        }
    }

    @Test
    public void testfindById(){
        User user = userDao.findById(6);
        System.out.println(user.getUid()+"\t"+user.getUname());
    }

    @Test
    public void testdelete(){
        int row = userDao.deleteById(4);
        System.out.println(row);
    }

    @Test
    public void testupdate(){
        User user = new User();
        user.setUname("练练");
        user.setSex("女");
        user.setPassword("12312");
        int row = userDao.update(user);
        System.out.println(row);
    }

    @Test
    public void testinsert(){
        User user = new User();
        user.setUname("得到");
        user.setSex("女");
        user.setPassword("12112");
        int row = userDao.insert(user);
        System.out.println(row);
    }


}