Jdbc配置文件连接mysql8.0——通过拼接字符串进行批量增删改操作

目录

一、基类BaseDao

二、对dog表的批量增删改操作

(一)Dog类

(二)DogDao接口

(三)DogDaoImpl实现类

1.批量新增

2.批量删除

3.批量修改

(四)Test测试

1.新增

2.删除

3.修改

三、对master表进行批量增删改

(一)Master类

(二)MasterDao接口

(三)MasterDaoImpl实现类

1.批量新增

2.批量删除

3.批量修改

(四)Test测试

1.新增

2.删除

3.修改

---

一、基类BaseDao package nj.zb.kb21.dao; import nj.zb.kb21.dao3.ConnectionPoolImpl; import nj.zb.kb21.utils.C3p0Util; import nj.zb.kb21.utils.DbConfig; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties; /** * 加载properties文件，实例化Connection对象，释放资源，执行公共方法 */ public class BaseDao { private static String driver; private static String url; private static String user; private static String pwd; static { Properties properties = new Properties(); InputStream inputStream = BaseDao.class.getClassLoader().getResourceAsStream("database.properties"); try { properties.load(inputStream); } catch (IOException e) { e.printStackTrace(); } driver = properties.getProperty("mysqldriver"); url = properties.getProperty("mysqlurl"); user = properties.getProperty("mysqluser"); pwd = properties.getProperty("mysqlpwd"); System.out.println(driver); System.out.println(url); System.out.println(user); System.out.println(pwd); } public Connection getConnection(){ Connection connection = null; try { Class.forName(driver); connection = DriverManager.getConnection(url, user, pwd); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return connection; } public void close(PreparedStatement preparedStatement, Connection connection, ResultSet resultSet){ try { if(null != preparedStatement){ preparedStatement.close(); } if(null != connection){ connection.close(); } if(null != resultSet){ resultSet.close(); } } catch (SQLException e) { e.printStackTrace(); } } public void close(PreparedStatement preparedStatement, Connection connection){ try { if(null != preparedStatement){ preparedStatement.close(); } if(null != connection){ connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } public void close(Connection connection){ try { if(null != connection){ connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } public int executeUpdate(String sqlStr,Object... params){ Connection connection = this.getConnection(); PreparedStatement preparedStatement = null; int num = -1; try { preparedStatement = connection.prepareStatement(sqlStr); if(null != params){ for (int i = 0;i < params.length;i++){ preparedStatement.setObject(i+1,params[i]); } } num = preparedStatement.executeUpdate(); }catch (SQLException e) { e.printStackTrace(); }finally { this.close(preparedStatement,connection); } return num; } public static void main(String[] args) { BaseDao baseDao = new BaseDao(); Connection connection = baseDao.getConnection(); System.out.println(connection+"连接成功！"); System.out.println("over"); } } 二、对dog表的批量增删改操作 (一)Dog类 package nj.zb.kb21.pojo; import java.util.Date; import java.util.List; public class Dog { // 数据库中的每一个字段映射为java类 // name,health,love, private Integer id; private String name; private Integer health; private Integer love; private String strain; private Date lytime; private List<Master> master;// 当前狗狗对象有过的主人信息 一对多 public List<Master> getMaster() { return master; } public void setMaster(List<Master> master) { this.master = master; } public Dog() { } public Dog(Integer id, String name, Integer health, Integer love, String strain, Date lytime) { this.id = id; this.name = name; this.health = health; this.love = love; this.strain = strain; this.lytime = lytime; } public Dog(String name, Integer health, Integer love, String strain) { this.name = name; this.health = health; this.love = love; this.strain = strain; } @Override public String toString() { return "Dog{" + "id=" + id + ", name='" + name + '\'' + ", health=" + health + ", love=" + love + ", strain='" + strain + '\'' + ", lytime=" + lytime + '}'; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getHealth() { return health; } public void setHealth(Integer health) { this.health = health; } public Integer getLove() { return love; } public void setLove(Integer love) { this.love = love; } public String getStrain() { return strain; } public void setStrain(String strain) { this.strain = strain; } public Date getLytime() { return lytime; } public void setLytime(Date lytime) { this.lytime = lytime; } } (二)DogDao接口 package nj.zb.kb21.dao; import nj.zb.kb21.pojo.Dog; import java.util.List; public interface DogDao { /** * * @param dogs 要新增的狗狗集合 * @return */ Integer batchSaveDog(List<Dog> dogs); /** * * @param ids 要删除的狗狗id集合 * @return */ Integer batchDelDog(List<Integer> ids); /** * * @param dogs 要批量修改的狗狗集合 * @return */ Integer batchUpdateDog(List<Dog> dogs); } (三)DogDaoImpl实现类 1.批量新增 package nj.zb.kb21.dao; import nj.zb.kb21.pojo.Dog; import nj.zb.kb21.pojo.Master; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class DogDaoImpl extends BaseDao implements DogDao{ // 批量新增 @Override public Integer batchSaveDog(List<Dog> dogs) { String sql = "insert into dog(name,health,love,strain,lytime) values"; for (Dog dog : dogs) { sql += "('"+dog.getName()+"',"+dog.getHealth()+","+dog.getLove()+",'"+dog.getStrain()+"',now()),"; } System.out.println(sql); sql = sql.substring(0,sql.length()-1); int num = super.executeUpdate(sql); return num; } } 2.批量删除 // 批量删除 @Override public Integer batchDelDogById(List<Integer> ids) { String sql = "delete from dog where id in ("; for (Integer idss : ids) { sql += idss+","; } sql = sql.substring(0,sql.length()-1); sql +=");"; System.out.println(sql); int num = super.executeUpdate(sql); System.out.println("根据id批量删除成功！"+num); return num; } 3.批量修改 // 批量修改 @Override public Integer batchUpdateDog(List<Dog> dogs) { // String sql = "update dog set name = n1,health = 20,love = 30 where id = 1"; String sql = ""; int num = 0; for (Dog dog : dogs ) { sql = "update dog set"; if(dog.getName()!=null&dog.getName()!=""){ sql += " name='"+dog.getName()+"',"; } if(dog.getHealth()!=null){ sql += " health="+dog.getHealth()+","; } if(dog.getLove()!=null){ sql += " love="+dog.getLove()+","; } if(dog.getStrain()!=null&dog.getStrain()!=""){ sql += " strain='"+dog.getStrain()+"',"; } sql = sql.substring(0,sql.length()-1)+" where id="+dog.getId()+";"; System.out.println(sql); num += super.executeUpdate(sql); } return num; } (四)Test测试 1.新增 @Test public void testBatchSaveDog(){ DogDaoImpl dogDao = new DogDaoImpl(); Dog dog1 = new Dog("a1", 100, 100, "t1"); Dog dog2 = new Dog("b1", 100, 90, "t2"); Dog dog3 = new Dog("c1", 100, 80, "t3"); ArrayList<Dog> dogs = new ArrayList<>(); dogs.add(dog1); dogs.add(dog2); dogs.add(dog3); dogDao.batchSaveDog(dogs); }

运行结果

2.删除 @Test public void testBatchDelDog(){ DogDaoImpl dogDao = new DogDaoImpl(); ArrayList<Integer> ids = new ArrayList<>(); ids.add(2); ids.add(3); ids.add(7); ids.add(8); dogDao.batchDelDogById(ids); } 3.修改 @Test public void testBatchUpdateDog(){ DogDaoImpl dogDao = new DogDaoImpl(); Dog dog1 = new Dog(); dog1.setId(54); dog1.setName(" "); dog1.setHealth(40); Dog dog2 = new Dog(); dog2.setId(55); dog2.setName("eee"); Dog dog3 = new Dog(); dog3.setId(56); dog3.setLove(77); dog3.setStrain("哈士奇"); ArrayList<Dog> dogs = new ArrayList<>(); dogs.add(dog1); dogs.add(dog2); dogs.add(dog3); Integer integer = dogDao.batchUpdateDog(dogs); System.out.println(integer); }

运行结果 

三、对master表进行批量增删改 (一)Master类 package nj.zb.kb21.pojo; public class Master { private Integer pid; private String name; private Integer age; private String gender; private Integer yearnum; private Integer did; private Dog dog; public Master(String name, Integer age, String gender, Integer yearnum, Integer did) { this.name = name; this.age = age; this.gender = gender; this.yearnum = yearnum; this.did = did; } public Master(Integer pid, String name, Integer age, String gender, Integer yearnum, Integer did, Dog dog) { this.pid = pid; this.name = name; this.age = age; this.gender = gender; this.yearnum = yearnum; this.did = did; this.dog = dog; } public Master() { } @Override public String toString() { return "Master{" + "pid=" + pid + ", name='" + name + '\'' + ", age=" + age + ", gender='" + gender + '\'' + ", yearnum=" + yearnum + ", did=" + did + ", dog=" + dog + '}'; } public Integer getPid() { return pid; } public void setPid(Integer pid) { this.pid = pid; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public Integer getYearnum() { return yearnum; } public void setYearnum(Integer yearnum) { this.yearnum = yearnum; } public Integer getDid() { return did; } public void setDid(Integer did) { this.did = did; } public Dog getDog() { return dog; } public void setDog(Dog dog) { this.dog = dog; } } (二)MasterDao接口 package nj.zb.kb21.dao; import nj.zb.kb21.pojo.Master; import java.util.List; public interface MasterDao { Integer batchSaveMaster(List<Master> masters); Integer batchDelMasterById(List<Integer> ids); Integer batchUpdateMaster(List<Master> masters); } (三)MasterDaoImpl实现类 1.批量新增 package nj.zb.kb21.dao; import nj.zb.kb21.pojo.Dog; import nj.zb.kb21.pojo.Master; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.List; public class MasterDaoImpl extends BaseDao implements MasterDao{ @Override public Integer batchSaveMaster(List<Master> masters) { String sql = "insert into master(name,age,gender,yearnum,did) values"; for (Master master : masters) { sql += "('"+master.getName()+"',"+master.getAge()+",'"+master.getGender()+"',"+master.getYearnum()+","+master.getDid()+"),"; } sql = sql.substring(0,sql.length()-1); System.out.println(sql); int num = super.executeUpdate(sql); System.out.println("批量新增成功"+num); return num; } } 2.批量删除 @Override public Integer batchDelMasterById(List<Integer> ids) { String sql = "delete from master where pid in ("; for (Integer idss : ids) { sql += idss+","; } sql = sql.substring(0,sql.length()-1); sql +=");"; System.out.println(sql); int num = super.executeUpdate(sql); System.out.println("根据pid批量删除成功！"+num); return num; } 3.批量修改 @Override public Integer batchUpdateMaster(List<Master> masters) { String sql = ""; int num = 0; for (Master master : masters) { sql = "update master set"; if(master.getName()!=null&master.getName()!=""){ sql += " name='"+master.getName()+"',"; } if(master.getAge()!=null){ sql += " age="+master.getAge()+","; } if(master.getGender()!=null&master.getGender()!=""){ sql += " gender='"+master.getGender()+"',"; } if(master.getYearnum()!=null){ sql += " yearnum="+master.getYearnum()+","; } if(master.getDid()!=null){ sql += " did="+master.getDid()+","; } sql = sql.substring(0,sql.length()-1)+" where pid="+master.getPid()+";"; System.out.println(sql); num += super.executeUpdate(sql); } System.out.println("更新成功！"); return num; } (四)Test测试 1.新增 @Test public void testBatchSaveMaster(){ MasterDaoImpl masterDao = new MasterDaoImpl(); Master master1 = new Master("m1", 20, "男", 2, 3); Master master2 = new Master("m2", 20, "男", 2, 3); Master master3 = new Master("m3", 20, "男", 2, 3); ArrayList<Master> masters = new ArrayList<>(); masters.add(master1); masters.add(master2); masters.add(master3); masterDao.batchSaveMaster(masters); }

运行结果

2.删除 @Test public void testBatchDelMaster(){ MasterDaoImpl masterDao = new MasterDaoImpl(); ArrayList<Integer> ids = new ArrayList<>(); ids.add(10); ids.add(11); ids.add(12); masterDao.batchDelMasterById(ids); } 3.修改 @Test public void testBatchUpdateMaster(){ MasterDaoImpl masterDao = new MasterDaoImpl(); Master master1 = new Master(); master1.setPid(13); master1.setName("name1"); master1.setYearnum(10); Master master2 = new Master(); master2.setPid(14); master2.setName("name2"); master2.setGender("女"); Master master3 = new Master(); master3.setPid(15); master3.setName("name3"); master3.setDid(60); ArrayList<Master> masters = new ArrayList<>(); masters.add(master1); masters.add(master2); masters.add(master3); Integer integer = masterDao.batchUpdateMaster(masters); System.out.println(integer); }

运行结果