5. SpringJDBC

JDBCTemplate JDBC的模板类

5.1. 搭建入门程序

5.1.1. 创建项目

5.1.2. Pom的依赖

xml

<!--Spring对JDBC封装-->
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.4.RELEASE</version>
</dependency>

<!-- 数据源： https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.7.0</version>
</dependency>

<!--msyql数据库的驱动-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>

5.1.3. 配置数据源

使用代码自己new
Spring-jdbc.xml IOC容器管理数据源 √

5.1.3.1. 创建xml文件

xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd ">

    <!-- 声明数据源-->
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
    </bean>
    
</beans>

5.1.3.2. 测试获取连接

java

import static org.junit.Assert.assertTrue;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.sql.DataSource;
import java.sql.SQLException;

/**
 * Unit test for simple App.
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:spring-jdbc.xml")
public class AppTest
{

    @Autowired
    DataSource dataSource;


    /**
     * 测试获取连接对象
     * @throws SQLException
     */
    @Test
    public void shouldAnswerWithTrue() throws SQLException {

        System.out.println(dataSource.getConnection());

    }
}

5.1.4. 使用JDBCTemplate类做测试查询

5.1.4.1. 自定义创建JDBCTemplate类

java

@Test
public void test2() throws SQLException {

    JdbcTemplate jt = new JdbcTemplate(dataSource);

    List<Map<String,Object>> userList = jt.queryForList("select * from t_user");
    for (Map<String, Object> userMap : userList) {
        Set<String> columns = userMap.keySet();

        for (String column : columns) {
            System.out.printf("column:%s,value:%s ",column,userMap.get(column));
        }
        System.out.println();
    }

}

5.1.4.2. 在IOC容器中定义JDBCTemplate类

xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd">


    <!-- 声明数据源-->
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" p:username="root">
<!--        <property name="username" value="root"/>-->
        <property name="password" value="root"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
    </bean>


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


    <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate"
        p:dataSource-ref="dataSource" >
    </bean>

</beans>

单元测试类

java

@Autowired
JdbcTemplate jt ;
@Test
public void test3() throws SQLException {

    List<Map<String,Object>> userList = jt.queryForList("select * from t_user");
    for (Map<String, Object> userMap : userList) {
        Set<String> columns = userMap.keySet();

        for (String column : columns) {
            System.out.printf("column:%s,value:%s ",column,userMap.get(column));
        }
        System.out.println();
    }
}

5.2. 使用jt封装Dao

5.2.1. 数据库脚本

sql

/*

SQLyog Ultimate v12.08 (64 bit)

MySQL - 5.7.18 : Database - mybatis

*********************************************************************

*/

 

 

/*!40101 SET NAMES utf8 */;

 

/*!40101 SET SQL_MODE=''*/;

 

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/`mybatis` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;

 

USE `mybatis`;

 

/*Table structure for table `t_account` */

 

DROP TABLE IF EXISTS `t_account`;

 

CREATE TABLE `t_account` (

  `account_id` int(11) NOT NULL AUTO_INCREMENT,

  `username` varchar(100) COLLATE utf8_bin DEFAULT NULL,

  `displayname` varchar(100) COLLATE utf8_bin DEFAULT NULL,

  `email` varchar(100) COLLATE utf8_bin DEFAULT NULL,

  `address` varchar(100) COLLATE utf8_bin DEFAULT '哈尔滨',

  `phone` varchar(100) COLLATE utf8_bin DEFAULT NULL,

  `createtime` decimal(10,0) DEFAULT NULL,

  PRIMARY KEY (`account_id`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='京东账户';

 

/*Data for the table `t_account` */

 

insert  into `t_account`(`account_id`,`username`,`displayname`,`email`,`address`,`phone`,`createtime`) values (1,'admin','郭靖','guojing@neusoft.com','襄阳城','18888888888','9999999999'),(2,'jshand','黄老邪','huanglaoxie@neusoft.com','桃花岛','13888888888','9999999999'),(4,'bbbb','梅超风','meichaofeng@163.com','骨髅岛','phone:138',NULL),(5,NULL,'黄蓉',NULL,'襄阳城',NULL,NULL),(6,NULL,'华筝',NULL,NULL,NULL,NULL),(7,NULL,'祥龙','insertSelective','哈尔滨',NULL,NULL),(8,'jjqqkk','伏虎','fuhu@163.com',NULL,NULL,NULL),(9,'jjqqkk',NULL,NULL,NULL,NULL,NULL),(10,NULL,'达摩',NULL,NULL,NULL,NULL),(11,'u-aaaaaaa','dis-aaaaaaa',NULL,'哈尔滨',NULL,NULL),(12,'u-bbbbbbbb',NULL,NULL,'哈尔滨',NULL,NULL),(13,'u-cccccccccc',NULL,NULL,'哈尔滨',NULL,NULL),(14,'u-cccccccccc','dis-aaaaaaa',NULL,'哈尔滨',NULL,NULL),(15,'u-dddddddddd--uuuu','dis-dddd--uuu','15@163.com','哈尔滨',NULL,NULL);

 

/*Table structure for table `t_order` */

 

DROP TABLE IF EXISTS `t_order`;

 

CREATE TABLE `t_order` (

  `order_id` int(11) NOT NULL AUTO_INCREMENT,

  `account_id` int(11) DEFAULT NULL,

  `order_amount` varchar(100) COLLATE utf8_bin DEFAULT NULL,

  `order_address` varchar(100) COLLATE utf8_bin DEFAULT NULL,

  `createtime` datetime DEFAULT NULL,

  PRIMARY KEY (`order_id`),

  KEY `FK_Reference_1` (`account_id`),

  CONSTRAINT `FK_Reference_1` FOREIGN KEY (`account_id`) REFERENCES `t_account` (`account_id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='订单表';

 

/*Data for the table `t_order` */

 

insert  into `t_order`(`order_id`,`account_id`,`order_amount`,`order_address`,`createtime`) values (1,1,'5000','襄阳城','2019-03-04 09:00:37'),(2,1,'6000','桃花岛','2019-03-12 09:00:54'),(3,2,'4000','桃花岛-岛主','2019-03-26 09:01:30'),(4,2,'8000','西域',NULL);

 

/*Table structure for table `t_order_item` */

 

DROP TABLE IF EXISTS `t_order_item`;

 

CREATE TABLE `t_order_item` (

  `pid` varchar(10) COLLATE utf8_bin DEFAULT NULL,

  `order_id` int(10) DEFAULT NULL,

  `itemid` varchar(10) COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`itemid`),

  KEY `FK_Reference_6` (`pid`),

  KEY `FK_Reference_5` (`order_id`),

  CONSTRAINT `FK_Reference_4` FOREIGN KEY (`pid`) REFERENCES `t_product` (`pid`),

  CONSTRAINT `FK_Reference_5` FOREIGN KEY (`order_id`) REFERENCES `t_order` (`order_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='订单详情\n';

 

/*Data for the table `t_order_item` */

 

insert  into `t_order_item`(`pid`,`order_id`,`itemid`) values ('1',1,'1'),('2',1,'2'),('3',1,'3'),('1',2,'4'),('4',2,'5'),('2',3,'6'),('3',3,'7');

 

/*Table structure for table `t_product` */

 

DROP TABLE IF EXISTS `t_product`;

 

CREATE TABLE `t_product` (

  `pid` varchar(10) COLLATE utf8_bin NOT NULL,

  `name` varchar(10) COLLATE utf8_bin DEFAULT NULL,

  `price` decimal(10,2) DEFAULT NULL,

  PRIMARY KEY (`pid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='商品信息';

 

/*Data for the table `t_product` */

 

insert  into `t_product`(`pid`,`name`,`price`) values ('1','男士腰带','180.00'),('2','香奈儿','50.00'),('3','生日蛋糕','120.00'),('4','联想笔记本电脑','5000.00'),('5','水杯','5.50');

 

/*Table structure for table `t_stu` */

 

DROP TABLE IF EXISTS `t_stu`;

 

CREATE TABLE `t_stu` (

  `stu_id` bigint(100) NOT NULL,

  `stu_name` varchar(100) NOT NULL COMMENT '学生姓名',

  `stu_birthday` date DEFAULT NULL COMMENT '学生出生日期',

  `stu_cardno` varchar(100) DEFAULT NULL COMMENT '学生身份证号码',

  `stu_gender` varchar(100) DEFAULT '男',

  PRIMARY KEY (`stu_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

/*Data for the table `t_stu` */

 

insert  into `t_stu`(`stu_id`,`stu_name`,`stu_birthday`,`stu_cardno`,`stu_gender`) values (1,'张国荣','1956-09-12','zgr0000000000001','男'),(2,'郭靖','1987-08-07','gj0000000000002','男'),(3,'黄蓉','1988-09-08','hr0000000000003','女'),(4,'黄老邪','2020-03-13','666556548465165','男');

 

/*Table structure for table `t_stu_account` */

 

DROP TABLE IF EXISTS `t_stu_account`;

 

CREATE TABLE `t_stu_account` (

  `account_id` bigint(50) NOT NULL COMMENT '账户id',

  `amount` double(10,2) DEFAULT '0.00' COMMENT '账户金额',

  `createtime` date DEFAULT NULL COMMENT '账户创建时间',

  `status` varchar(50) DEFAULT NULL COMMENT '账户状态',

  `stu_id` bigint(50) NOT NULL COMMENT '学生id',

  PRIMARY KEY (`account_id`),

  KEY `fk_account_stu` (`stu_id`),

  CONSTRAINT `fk_account_stu` FOREIGN KEY (`stu_id`) REFERENCES `t_stu` (`stu_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

/*Data for the table `t_stu_account` */

 

insert  into `t_stu_account`(`account_id`,`amount`,`createtime`,`status`,`stu_id`) values (1,15.15,'2018-01-01','销户',1),(2,80.15,'2018-02-03','正常',2),(3,190.15,'2015-06-10','挂失',3);

 

/*Table structure for table `t_user` */

 

DROP TABLE IF EXISTS `t_user`;

 

CREATE TABLE `t_user` (

  `user_id` varchar(100) COLLATE utf8_bin NOT NULL,

  `user_name` varchar(100) COLLATE utf8_bin DEFAULT NULL,

  `gender` varchar(100) COLLATE utf8_bin DEFAULT NULL,

  `birthday` datetime DEFAULT NULL,

  `status` varchar(10) COLLATE utf8_bin DEFAULT NULL,

  PRIMARY KEY (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 

/*Data for the table `t_user` */

 

insert  into `t_user`(`user_id`,`user_name`,`gender`,`birthday`,`status`) values ('1','刘备','男','2008-02-02 09:57:43','0'),('10','典韦使用别名','男','2020-03-11 15:01:03','0'),('11','典韦使用别名','男','2020-03-12 08:45:53','0'),('12','root','男','2020-03-12 08:47:02','0'),('13','root','男','2020-03-12 08:50:53','0'),('14','曹操','男','2020-03-12 09:04:19','0'),('15','曹操1','男','2020-03-12 09:07:31','0'),('20','使用JT添加的',NULL,NULL,NULL),('3','关云长','男','2008-02-02 09:57:43','0'),('5','三国-典韦','男','2020-03-11 13:39:39','0'),('6','典韦使用别名','男','2020-03-11 14:47:12','0'),('7','典韦使用别名','男','2020-03-11 14:55:42','0'),('8','典韦使用别名','男','2020-03-11 14:56:24','0'),('9','吕布',NULL,NULL,NULL);

 

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295

5.2.2. 实体类User

java

import java.util.Date;

/**
 * 用户的实体对象
 */
public class User {

    private String userId;
    private String userName;
    private String gender;
    private Date birthday;
    private Integer status;

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId='" + userId + '\'' +
                ", userName='" + userName + '\'' +
                ", gender='" + gender + '\'' +
                ", birthday=" + birthday +
                ", status=" + status +
                '}';
    }
}

5.2.3. 定义Dao

java

import org.springframework.jdbc.core.JdbcTemplate;

/**
 * 项目    ： spring-java1
 * 创建时间 ：2020/3/24  13:40 24
 * author  ：张金山
 * site    :   https://jshand.gitee.io
 * 描述     : 用户的Dao层操作
 */
public class UserDao {


    //让Dao层持有一个 jt
    private JdbcTemplate jdbcTemplate;


    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    //写增删改查的代码
}

5.2.4. 声明Bean并注入jt

xml

<bean id="userDao" class="com.neuedu.UserDao">
    <property name="jdbcTemplate" ref="jt"/>
</bean>

5.2.5. 单元测试UserDao的实例化对象是否声明完成

java

//使用jdbcTemplate测试CRUD
@Autowired
UserDao userDao;

@Test
public  void testUserDao(){
    System.out.println(userDao.getJdbcTemplate());
}

5.2.6. Insert

5.2.6.1. Dao中声明的方法

java

/**
 * 插入用户信息
 * @param user
 * @return
 */
public boolean insert(User user){
    String sql = "INSERT INTO t_user (  user_id,  user_name, gender,  birthday , status )  VALUES  (  ?,?,?,?,? ) ";

    Object[] params = new Object[]{
            user.getUserId(),
            user.getUserName(),
            user.getGender(),
            user.getBirthday(),
            user.getStatus(),
    };
    int count = jdbcTemplate.update(sql,params);

    return count>0;
}

5.2.6.2. 单元测试类

java

@Test
public  void testInsert(){

    User user = new User();
    user.setUserId("20");
    user.setUserName("使用JT添加的");
    boolean success = userDao.insert(user);
    System.out.println(success);

}

5.2.7. delete

5.2.7.1. Dao中声明的方法

java

/**
 * 删除
 * @param id
 * @return
 */
public boolean delete(String id){
    String sql = "delete from  t_user where  user_id = ? ";

    int count = jdbcTemplate.update(sql,id);

    return count>0;
}

5.2.7.2. 单元测试类

java

@Test
public  void testDelete(){

   String id = "16";
   boolean success = userDao.delete(id);
   System.out.println(success);
}

5.2.8. selectById

5.2.8.1. Dao中声明的方法

java

/**
     * 根据主键查询一条
     * @param id
     * @return
     */
    public User selectById(String id){

        String sql = "select * from t_user where user_id = ?";

        //聚合函数的时候使用并且是单行、单列的
//        User user = jdbcTemplate.queryForObject(sql,User.class,id);
        User user = jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                String userId = resultSet.getString("user_id");
                String userName = resultSet.getString("user_name");
                //其他属性照抄 String userName = resultSet.getString("user_name");


                User user = new User();
                user.setUserId(userId);
                user.setUserName(userName);
                return user;
            }
        },id);

        return user;

    }

5.2.8.2. 单元测试类

java

@Test
public  void testSelectById(){

    String id = "1";
    User user =  userDao.selectById(id);
    System.out.println(user);
}

5.2.9. selectList

5.2.9.1. Dao中声明的方法

java

/**
 * 查询多条
 * @param
 * @return
 */
public List selectList(){

    String sql = "select * from t_user ";

    List<User> userList= jdbcTemplate.queryForObject(sql, new RowMapper<List>() {
        @Override
        public List<User> mapRow(ResultSet resultSet, int i) throws SQLException {

            List<User> list = new ArrayList();
            do {
                String userId = resultSet.getString("user_id");
                String userName = resultSet.getString("user_name");
                //其他属性照抄 String userName = resultSet.getString("user_name");

                User user = new User();
                user.setUserId(userId);
                user.setUserName(userName);
                list.add(user);
            }while(resultSet.next());

            return list;

        }
    });
    return userList;
}

5.2.9.2. 单元测试类

java

@Test
public  void testSelectList(){
    List<User> list =  userDao.selectList();

    //应该在页面列表展示
    System.out.println(list.size());

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

5.2.10. update

5.2.10.1. Dao中声明的方法

java

/**
 * 根据主键更新
 * @param user
 * @return
 */
public boolean update(User user){
    StringBuffer sql = new StringBuffer();

    sql.append(" UPDATE          ");
    sql.append("  t_user            ");
    sql.append(" SET                ");
    sql.append("   user_name=?,       ");
    sql.append("   gender=?,      ");
    sql.append("   birthday=?,    ");
    sql.append("   status=?           ");
    sql.append(" WHERE user_id=?   ");


    Object[] params = new Object[]{

            user.getUserName(),
            user.getGender(),
            user.getBirthday(),
            user.getStatus(),
            user.getUserId()
    };
    //update -- delete 、insert、update
    int count = jdbcTemplate.update(sql.toString(),params);

    return count>0;
}

5.2.10.2. 单元测试类

java

@Test
public  void testUpdate(){

    String id = "9";
    User user = userDao.selectById(id);
    user.setUserName("吕布");

    boolean success = userDao.update(user);

    System.out.println("success:"+success);
}

5.2.11. 完整的UserDao代码

java

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 项目    ： spring-java1
 * 创建时间 ：2020/3/24  13:40 24
 * author  ：张金山
 * site    :   https://jshand.gitee.io
 * 描述     : 用户的Dao层操作
 */
public class UserDao {


    //让Dao层持有一个 jt
    private JdbcTemplate jdbcTemplate;


    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;



    }

    //写增删改查的代码

    /**
     * 插入用户信息
     * @param user
     * @return
     */
    public boolean insert(User user){
        String sql = "INSERT INTO t_user (  user_id,  user_name, gender,  birthday , status )  VALUES  (  ?,?,?,?,? ) ";

        Object[] params = new Object[]{
                user.getUserId(),
                user.getUserName(),
                user.getGender(),
                user.getBirthday(),
                user.getStatus(),
        };
        int count = jdbcTemplate.update(sql,params);

        return count>0;
    }

    /**
     * 删除
     * @param id
     * @return
     */
    public boolean delete(String id){
        String sql = "delete from  t_user where  user_id = ? ";

        int count = jdbcTemplate.update(sql,id);

        return count>0;
    }


    /**
     * 根据主键查询一条
     * @param id
     * @return
     */
    public User selectById(String id){

        String sql = "select * from t_user where user_id = ?";

        //聚合函数的时候使用并且是单行、单列的
//        User user = jdbcTemplate.queryForObject(sql,User.class,id);
        User user = jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                String userId = resultSet.getString("user_id");
                String userName = resultSet.getString("user_name");
                //其他属性照抄 String userName = resultSet.getString("user_name");


                User user = new User();
                user.setUserId(userId);
                user.setUserName(userName);
                return user;
            }
        },id);

        return user;

    }

    /**
     * 查询多条
     * @param
     * @return
     */
    public List selectList(){

        String sql = "select * from t_user ";

        List<User> userList= jdbcTemplate.queryForObject(sql, new RowMapper<List>() {
            @Override
            public List<User> mapRow(ResultSet resultSet, int i) throws SQLException {

                List<User> list = new ArrayList();
                do {
                    String userId = resultSet.getString("user_id");
                    String userName = resultSet.getString("user_name");
                    //其他属性照抄 String userName = resultSet.getString("user_name");

                    User user = new User();
                    user.setUserId(userId);
                    user.setUserName(userName);
                    list.add(user);
                }while(resultSet.next());

                return list;

            }
        });
        return userList;
    }


    /**
     * 根据主键更新
     * @param user
     * @return
     */
    public boolean update(User user){
        StringBuffer sql = new StringBuffer();

        sql.append(" UPDATE          ");
        sql.append("  t_user            ");
        sql.append(" SET                ");
        sql.append("   user_name=?,       ");
        sql.append("   gender=?,      ");
        sql.append("   birthday=?,    ");
        sql.append("   status=?           ");
        sql.append(" WHERE user_id=?   ");


        Object[] params = new Object[]{

                user.getUserName(),
                user.getGender(),
                user.getBirthday(),
                user.getStatus(),
                user.getUserId()
        };
        //update -- delete 、insert、update
        int count = jdbcTemplate.update(sql.toString(),params);

        return count>0;
    }
}

5.2.12. 完整的spring-jdbc.xml文件

java

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd">


    <!-- 声明数据源-->
    <!--<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" >
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>

    </bean>-->


    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"
        p:username="root"
        p:password="root"
        p:driverClassName="com.mysql.jdbc.Driver"
        p:url="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&unic" >

    </bean>



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


    <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate"
        p:dataSource-ref="dataSource" >
    </bean>


    <bean id="userDao" class="com.neuedu.UserDao">
        <property name="jdbcTemplate" ref="jt"/>
    </bean>
    

</beans>

5.2.13. 完整的单元测试类

java

package com.neuedu;

import static org.junit.Assert.assertTrue;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Unit test for simple App.
 */
@RunWith(SpringJUnit4ClassRunner.class) // junit 4.12+
@ContextConfiguration(locations = "classpath:spring-jdbc.xml")
public class AppTest
{

    @Autowired
    DataSource dataSource;


    /**
     * 测试获取连接对象
     * @throws SQLException
     */
    @Test
    public void test1() throws SQLException {

        System.out.println(dataSource.getConnection());
    }

    @Test
    public void test2() throws SQLException {

        JdbcTemplate jt = new JdbcTemplate(dataSource);

        List<Map<String,Object>> userList = jt.queryForList("select * from t_user");
        for (Map<String, Object> userMap : userList) {
            Set<String> columns = userMap.keySet();

            for (String column : columns) {
                System.out.printf("column:%s,value:%s ",column,userMap.get(column));
            }
            System.out.println();
        }

    }



    @Autowired
    JdbcTemplate jt ;
    @Test
    public void test3() throws SQLException {

        List<Map<String,Object>> userList = jt.queryForList("select * from t_user");
        for (Map<String, Object> userMap : userList) {
            Set<String> columns = userMap.keySet();

            for (String column : columns) {
                System.out.printf("column:%s,value:%s ",column,userMap.get(column));
            }
            System.out.println();
        }
    }




    //使用jdbcTemplate测试CRUD
    @Autowired
    UserDao userDao;

    @Test
    public  void testUserDao(){
        System.out.println(userDao.getJdbcTemplate());
    }

    @Test
    public  void testInsert(){

        User user = new User();
        user.setUserId("20");
        user.setUserName("使用JT添加的");
        boolean success = userDao.insert(user);
        System.out.println(success);

    }


    @Test
    public  void testDelete(){

       String id = "16";
       boolean success = userDao.delete(id);
       System.out.println(success);

    }

    @Test
    public  void testSelectById(){

        String id = "1";
        User user =  userDao.selectById(id);
        System.out.println(user);

    }


    @Test
    public  void testSelectList(){
        List<User> list =  userDao.selectList();

        //应该在页面列表展示
        System.out.println(list.size());

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

    @Test
    public  void testUpdate(){

        String id = "9";
        User user = userDao.selectById(id);
        user.setUserName("吕布");

        boolean success = userDao.update(user);

        System.out.println("success:"+success);
    }

}

5. SpringJDBC ​

5.1. 搭建入门程序 ​

5.1.1. 创建项目 ​

5.1.2. Pom的依赖 ​

5.1.3. 配置数据源 ​

5.1.3.1. 创建xml文件 ​

5.1.3.2. 测试获取连接 ​

5.1.4. 使用JDBCTemplate类做测试查询 ​

5.1.4.1. 自定义创建JDBCTemplate类 ​

5.1.4.2. 在IOC容器中定义JDBCTemplate类 ​

5.2. 使用jt封装Dao ​

5.2.1. 数据库脚本 ​

5.2.2. 实体类User ​

5.2.3. 定义Dao ​

5.2.4. 声明Bean并注入jt ​

5.2.5. 单元测试UserDao的实例化对象是否声明完成 ​

5.2.6. Insert ​

5.2.6.1. Dao中声明的方法 ​

5.2.6.2. 单元测试类 ​

5.2.7. delete ​

5.2.7.1. Dao中声明的方法 ​

5.2.7.2. 单元测试类 ​

5.2.8. selectById ​

5.2.8.1. Dao中声明的方法 ​

5.2.8.2. 单元测试类 ​

5.2.9. selectList ​

5.2.9.1. Dao中声明的方法 ​

5.2.9.2. 单元测试类 ​

5.2.10. update ​

5.2.10.1. Dao中声明的方法 ​

5.2.10.2. 单元测试类 ​

5.2.11. 完整的UserDao代码 ​

5.2.12. 完整的spring-jdbc.xml文件 ​

5.2.13. 完整的单元测试类 ​

5. SpringJDBC

5.1. 搭建入门程序

5.1.1. 创建项目

5.1.2. Pom的依赖

5.1.3. 配置数据源

5.1.3.1. 创建xml文件

5.1.3.2. 测试获取连接

5.1.4. 使用JDBCTemplate类做测试查询

5.1.4.1. 自定义创建JDBCTemplate类

5.1.4.2. 在IOC容器中定义JDBCTemplate类

5.2. 使用jt封装Dao

5.2.1. 数据库脚本

5.2.2. 实体类User

5.2.3. 定义Dao

5.2.4. 声明Bean并注入jt

5.2.5. 单元测试UserDao的实例化对象是否声明完成

5.2.6. Insert

5.2.6.1. Dao中声明的方法

5.2.6.2. 单元测试类

5.2.7. delete

5.2.7.1. Dao中声明的方法

5.2.7.2. 单元测试类

5.2.8. selectById

5.2.8.1. Dao中声明的方法

5.2.8.2. 单元测试类

5.2.9. selectList

5.2.9.1. Dao中声明的方法

5.2.9.2. 单元测试类

5.2.10. update

5.2.10.1. Dao中声明的方法

5.2.10.2. 单元测试类

5.2.11. 完整的UserDao代码

5.2.12. 完整的spring-jdbc.xml文件

5.2.13. 完整的单元测试类