Spring中 用JdbcTemplate & 德鲁伊池实现数据库操作

JdbcTemplate&德鲁伊池 进行数据库操作 – Spring

---

1. 数据库连接

1. 数据库连接参数

   db.url = jdbc:mysql://localhost:3306/dreamhome?serverTimezone = GMT
   db.username = root
   db.password = root
   db.driverClassName = com.mysql.cj.jdbc.Driver
   

   • 参数一般使用property文件单独抽取出来
   • property 文件中，以key = value的形式配置参数
   • value值默认有双引号，一定不要自己加双引号，否则会将双引号当作value的一部分，造成数据库连接不上的BUG

2. 德鲁伊池配置

       <!--引入property文件-->
       <context:property-placeholder location="classpath:jdbc_template/demo1/DB.properties"/>
   
       <!--数据库连接-->
       <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
             destroy-method="close">
           <property name="url" value="${db.url}" />
           <property name="username" value="${db.username}" />
           <property name="password" value="${db.password}" />
           <property name="driverClassName" value="${db.driverClassName}" />
       </bean>
   

3. JdbcTemplate 对象创建

       <bean id = "jdbcTemplate" class = "org.springframework.jdbc.core.JdbcTemplate">
           <!--注入dataSource-->
           <property name="dataSource" ref = "dataSource"/>
       </bean>
   

2. 增删改查

// User类
public class User {
    public void setId(int id) {this.id = id;}
    public void setName(String name) {this.name = name;}
    public int id;
    public String name;
}


// 插入一行数据
String sql = "insert into user values(?,?)";
int ret = jdbcTemplate.update(sql,user.id,user.name);

// 删除数据
String sql = "delete from user where id = ?";
int ret = jdbcTemplate.update(sql,user.id);

//更新数据
String sql = "update user set name = ? where id = ?";
int ret = jdbcTemplate.update(sql,user.name,user.id);

//查找单个值(不一定是Integer，String等都可以)
String sql = "select count(*) from user";
Integer ret = jdbcTemplate.queryForObject(sql,Integer.class);

//查找一个元组，并封装成对象返回
String sql = "select * from user where id = ?";
User user =  jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),id);

// 查找表中所有数据，以对象数组形式返回
 String sql = "select * from user";
List<User> users = jdbcTemplate.query(sql,new BeanPropertyRowMapper<User>(User.class));

//查找某一列元素，返回一个List列表
String sql = "select name from user";
List<String> names = jdbcTemplate.queryForList(sql,String.class);

//批量添加
List<Object[]> args = new ArrayList<Object[]>();
Object[] o1 = {2019211956,"zhang_san"};
Object[] o2 = {2019211957,"li_si"};
Object[] o3 = {2019211958,"wang_wu"};
args.add(o1);
args.add(o2);
args.add(o3);
String sql = "insert into user values(?,?)";
int []ret = jdbcTemplate.batchUpdate(sql, args);

// 批量删除
List<Object[]> args = new ArrayList<Object[]>();
Object[] o1 = {2019211956};
Object[] o2 = {2019211957};
Object[] o3 = {2019211958};
args.add(o1);
args.add(o2);
args.add(o3);
userService.batchDeleteUsers(args);
String sql = "delete from user where id = ?";
jdbcTemplate.batchUpdate(sql, args);

//批量更新
List<Object[]> args = new ArrayList<Object[]>();
Object[] o1 = {2019211956,"zhang_san001",2019211956};
Object[] o2 = {2019211957,"li_si001",2019211957};
Object[] o3 = {2019211958,"wang_wu001",2019211958};
args.add(o1);
args.add(o2);
args.add(o3);
String sql = "update user set id = ?,name = ? where id = ?";
jdbcTemplate.batchUpdate(sql, args);