4028连接数据库的增删改成查

于 2020-06-29 13:20:51 发布
阅读量796 收藏
点赞数
分类专栏： 笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/chf605908340/article/details/106046244
版权
本文介绍了一个使用Java实现的班级管理系统，包括班级实体类的定义、DAO接口及其实现，以及数据库连接和测试方法。系统提供了增删改查等功能，通过MySql数据库进行数据持久化。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

对班级表的设置：

package cn.edu.mju.project1.entity;

import java.util.List;

/**
 * @ProjectName: Project1
 * @Package: cn.edu.mju.project1.entity
 * @ClassName: Band
 * @Author: Dell
 * @Date: 2020/4/28 12:42
 */
public class Band {
    private int id;
    private String name;
    private String remark;

    public Band(){

    }
    public Band(int id , String name ,String remark ){
        this.id =id;
        this.name = name;
        this.remark = remark;
    }

    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 getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }
}

接口(便于对表的调用)：

package cn.edu.mju.project1.persiste;

import cn.edu.mju.project1.entity.Band;
import cn.edu.mju.project1.util.Pager;

import java.util.List;

/**
 * @ProjectName: Project1
 * @Package: cn.edu.mju.project1.persiste
 * @ClassName: IBandDao
 * @Author: Dell
 * @Date: 2020/5/9 9:14
 */
public interface IBandDao {
    //插入数据

    public boolean insert(Band band);

    //根据主键删除记录

    public boolean delete(Integer id);

    //根据主键修改数据

    public boolean update(Band band);

    //查询数据,根据主键

    public  Band findById(Integer id);


    //查询所有数据

    public List<Band> findAll();

    //根据条件查询相关的数据

    public List<Band> find(Band band);

}

对student表的操作：

package cn.edu.mju.project1.persiste.impl;

import cn.edu.mju.project1.entity.Band;
import cn.edu.mju.project1.util.Pager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @ProjectName: Project1
 * @Package: cn.edu.mju.project1.persiste.impl
 * @ClassName: IBandDao
 * @Author: Dell
 * @Date: 2020/5/9 9:20
 */
public class IBandDao implements cn.edu.mju.project1.persiste.IBandDao {
    @Override
    public boolean insert(Band band) {
        boolean str = false;
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "INSERT INTO band(name,remark) VALUES(?,?)";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, band.getName());
            pstmt.setString(2, band.getRemark());
            int result = pstmt.executeUpdate();
            System.out.println(result);
            if (result > 0) {
                str = true;
            }
            pstmt.close();
            pstmt.close();

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

    @Override
    public boolean delete(Integer id) {
        boolean ret = false;
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "DELETE from band WHERE id = ?";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            //第一个，id为3；
            pstmt.setInt(1, id);
            int result = pstmt.executeUpdate();
            if (result > 0) {
                ret = true;
            }
            pstmt.close();
            connection.close();

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

    @Override
    public boolean update(Band band) {
        boolean ret = false;
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "UPDATE band SET name = ?,remark = ? WHERE id = ?";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, band.getName());
            pstmt.setString(2, band.getRemark());
            pstmt.setInt(3, band.getId());
            int result = pstmt.executeUpdate();
            if (result > 0) {
                ret = true;

            } else {
                ret = false;
            }
            pstmt.close();
            connection.close();

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


    //不是泛型的原因:id是唯一的只会返回一条数据

    @Override
    public Band findById(Integer id) {
        Band band = null;
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band WHERE id = ? ";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet result = pstmt.executeQuery();
            if (result.next()) {
                band = new Band();
                band.setId(result.getInt("id"));
                band.setName(result.getString("name"));
                band.setRemark(result.getString("remark"));
            }
            result.close();
            pstmt.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return band;
    }

    @Override
    public List<Band> findAll() {
        List<Band> bands = new ArrayList<>();
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet result = pstmt.executeQuery();
            while (result.next()) {
                Band band = new Band();
                band.setId(result.getInt("id"));
                band.setName(result.getString("name"));
                band.setRemark(result.getString("remark"));
                //将band中的信息添加到链表中
                bands.add(band);
            }
            result.close();
            pstmt.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bands;
    }

    //查询接口类的相关数据

    @Override
    public List<Band> find(Band band){
        List<Band> bands = new ArrayList<>();
        List<Object> parms = new ArrayList<>();
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band WHERE 1 = 1  ";

            if (band != null && !"".equals(band.getName())) {
                //两种情况
                if (band.getName() != null && !"".equals(band.getName())) {
                    sql = sql + "AND NAME LIKE ?";
                    parms.add("%" + band.getName() + "%" );
                }
                if (band.getRemark() != null && !"".equals(band.getRemark())) {
                    sql = sql + "and remark like ?";
                    parms.add("%" + band.getRemark() + "%");
                }
            }
            //获取sql语句
            PreparedStatement pstmt = connection.prepareStatement(sql);
            for (int i = 0; i < parms.size(); i++) {
                pstmt.setObject(i+1,parms.get(i));
            }
            //获取结果
            ResultSet result = pstmt.executeQuery();
            while (result.next()) {
                band = new Band();
                band.setId(result.getInt("id"));
                band.setName(result.getString("name"));
                band.setRemark(result.getString("remark"));
                bands.add(band);
            }
            result.close();
            pstmt.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bands;
    }
    }

对数据库的连接：

package cn.edu.mju.project1.persiste.impl;

import com.alibaba.druid.pool.DruidDataSource;

import java.sql.Connection;

/**
 * @ProjectName: Project1
 * @Package: cn.edu.mju.project1
 * @ClassName: MySqlDbUtil
 * @Author: Dell
 * @Date: 2020/4/27 16:59
 */
public class MySqlDbUtil {
    private static DruidDataSource dataSource = null;
    private static void initDateSource() throws Exception{
        if (dataSource == null){
            dataSource = new DruidDataSource();
            dataSource.setUrl("jdbc:mysql://localhost:3306/band1");
            dataSource.setUsername("root");
            dataSource.setPassword("hcf12194733");
            dataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dataSource.setInitialSize(2);
            dataSource.setMinIdle(2);
            dataSource.setMaxActive(18);
            dataSource.setMaxWait(2000);
            dataSource.setTimeBetweenConnectErrorMillis(2000);
            dataSource.setValidationQuery("x");
            dataSource.setTestWhileIdle(true);
            dataSource.setTestOnBorrow(true);
        }
    }
    public static Connection getConnection() throws Exception{
        initDateSource();
        return dataSource.getConnection();
    }

}

对数据库的测试：

import cn.edu.mju.project1.persiste.impl.MySqlDbUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @ProjectName: Project1
 * @Package: PACKAGE_NAME
 * @ClassName: TestDbUtil
 * @Author: Dell
 * @Date: 2020/4/27 17:20
 */
public class TestDbUtil {
    @Test
    public void testConnection() {
        try {
            MySqlDbUtil.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void testInsert() {
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "INSERT INTO band(name,remark) VALUES(?,?)";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, "软件1班");
            pstmt.setString(2, "备注信息");
            int result = pstmt.executeUpdate();
            if (result > 0) {
                System.out.println("添加成功");

            } else {
                System.out.println("添加失败");
            }
            pstmt.close();
            pstmt.close();

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

    @Test
    public void testUpDate() {
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "UPDATE band SET name = ?,remark = ? WHERE id = ?";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, "软件1班");
            pstmt.setString(2, "备注信息");
            pstmt.setInt(3, 1);
            int result = pstmt.executeUpdate();
            if (result > 0) {
                System.out.println("修改成功");

            } else {
                System.out.println("修改失败");
            }
            pstmt.close();
            pstmt.close();

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

    @Test
    public void testDelete() {
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "DELETE from band WHERE id = ?";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            //第一个，id为3；
            pstmt.setInt(1, 3);
            int result = pstmt.executeUpdate();
            if (result > 0) {
                System.out.println("删除成功");

            } else {
                System.out.println("删除失败");
            }
            pstmt.close();
            pstmt.close();

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

    @Test
    //查询所有
    public void findAll() {
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet result = pstmt.executeQuery();
            while (result.next()) {
                int id = result.getInt("id");
                String name = result.getString("name");
                String remark = result.getString("remark");
                System.out.println(id + "," + name + "," + remark);
            }
            result.close();
            pstmt.close();
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    //查询单条记录
    public void testFindById() {
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band WHERE id = ?";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, 2);
            //第一个参数，id为3；
            ResultSet result = pstmt.executeQuery();
            while (result.next()) {
                int id = result.getInt("id");
                String name = result.getString("name");
                String remark = result.getString("remark");
                System.out.println(id + "," + name + "," + remark);
            }
            result.close();
            pstmt.close();
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    //模糊查询
    public void TestFindByName() {
        try {
            Connection connection = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band WHERE name like ?";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, "%1%");
            //第一个参数，id为3；
            ResultSet result = pstmt.executeQuery();
            while (result.next()) {
                int id = result.getInt("id");
                String name = result.getString("name");
                String remark = result.getString("remark");
                System.out.println(id + "," + name + "," + remark);
            }
            result.close();
            pstmt.close();
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值