认识一个框架的用途和作用,其实很简单。反其道而行之,如果不用某框架,我们的开发工作会怎么样?我们必须要做那些额外的工作。
为了保证系列的完整性,也专门写一篇文章,作为本系列的引子。
考虑到简单性和专一性,本文准备的例子,弱到几点。
主要功能
-
通过比对,了解spring JdbcTemplate的功能,作为Spring Data的入门。
-
搭建一个实验的例子,作为将来学习Spring Data的基础。
-
暂时不考虑事务(事务可以很简单通过AOP方式切入进来)
1.准备
JDK:1.7
spring:4.0.2(无所谓 spring3也可以)
database(mysql 5.5)
代码片段片段:http://www.journaldev.com/2593/spring-jdbc-example
1.1Database Setup
1
2
3
4
5
|
CREATE
TABLE
`Employee` (
`id`
int
(11) unsigned
NOT
NULL
,
`
name
`
varchar
(20)
DEFAULT
NULL
,
`role`
varchar
(20)
DEFAULT
NULL
,
PRIMARY
KEY
(`id`)) ENGINE=InnoDB
DEFAULT
CHARSET=utf8;
|
1.2 maven pom.xml
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
|
<
project
xmlns
=
"http://maven.apache.org/POM/4.0.0"
xmlns:xsi
=
"http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation
=
"http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"
>
<
modelVersion
>4.0.0</
modelVersion
>
<
groupId
>org.springframework.samples</
groupId
>
<
artifactId
>SpringJDBCExample</
artifactId
>
<
version
>0.0.1-SNAPSHOT</
version
>
<
properties
>
<!-- Generic properties -->
<
java.version
>1.7</
java.version
>
<
project.build.sourceEncoding
>UTF-8</
project.build.sourceEncoding
>
<
project.reporting.outputEncoding
>UTF-8</
project.reporting.outputEncoding
>
<!-- Spring -->
<
spring-framework.version
>4.0.2.RELEASE</
spring-framework.version
>
<!-- Logging -->
<
logback.version
>1.0.13</
logback.version
>
<
slf4j.version
>1.7.5</
slf4j.version
>
</
properties
>
<
build
>
<
resources
>
<
resource
>
<
directory
>src/main/resources</
directory
>
<
filtering
>true</
filtering
>
<
includes
>
<
include
>**/*.xml</
include
>
<
include
>**/*</
include
>
</
includes
>
</
resource
>
</
resources
>
<
plugins
>
<
plugin
>
<
groupId
>org.apache.maven.plugins</
groupId
>
<
artifactId
>maven-compiler-plugin</
artifactId
>
<
version
>2.3.2</
version
>
<
configuration
>
<
source
>1.7</
source
>
<
target
>1.7</
target
>
<
showWarnings
>true</
showWarnings
>
<
encoding
>utf-8</
encoding
>
</
configuration
>
</
plugin
>
</
plugins
>
</
build
>
<
dependencies
>
<!-- Spring and Transactions -->
<
dependency
>
<
groupId
>org.springframework</
groupId
>
<
artifactId
>spring-context</
artifactId
>
<
version
>${spring-framework.version}</
version
>
</
dependency
>
<
dependency
>
<
groupId
>org.springframework</
groupId
>
<
artifactId
>spring-tx</
artifactId
>
<
version
>${spring-framework.version}</
version
>
</
dependency
>
<!-- Spring JDBC Support -->
<
dependency
>
<
groupId
>org.springframework</
groupId
>
<
artifactId
>spring-jdbc</
artifactId
>
<
version
>${spring-framework.version}</
version
>
</
dependency
>
<
dependency
>
<
groupId
>org.springframework</
groupId
>
<
artifactId
>spring-test</
artifactId
>
<
version
>${spring-framework.version}</
version
>
</
dependency
>
<!-- MySQL Driver -->
<
dependency
>
<
groupId
>mysql</
groupId
>
<
artifactId
>mysql-connector-java</
artifactId
>
<
version
>5.0.5</
version
>
</
dependency
>
<!-- Logging with SLF4J & LogBack -->
<
dependency
>
<
groupId
>log4j</
groupId
>
<
artifactId
>log4j</
artifactId
>
<
version
>1.2.17</
version
>
</
dependency
>
<
dependency
>
<
groupId
>org.slf4j</
groupId
>
<
artifactId
>slf4j-api</
artifactId
>
<
version
>${slf4j.version}</
version
>
<
scope
>compile</
scope
>
</
dependency
>
<
dependency
>
<
groupId
>ch.qos.logback</
groupId
>
<
artifactId
>logback-classic</
artifactId
>
<
version
>${logback.version}</
version
>
<
scope
>runtime</
scope
>
</
dependency
>
<
dependency
>
<
groupId
>junit</
groupId
>
<
artifactId
>junit</
artifactId
>
<
version
>4.10</
version
>
</
dependency
>
</
dependencies
>
</
project
>
|
log4j.xml(随便找了一个)
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
|
<?
xml
version
=
"1.0"
encoding
=
"UTF-8"
?>
<!DOCTYPE log4j:configuration PUBLIC "-//LOGGER" "log4j.dtd">
<
log4j:configuration
xmlns:log4j
=
"http://jakarta.apache.org/log4j/"
>
<!-- Appenders -->
<
appender
name
=
"console"
class
=
"org.apache.log4j.ConsoleAppender"
>
<
param
name
=
"Target"
value
=
"System.out"
/>
<
layout
class
=
"org.apache.log4j.PatternLayout"
>
<
param
name
=
"ConversionPattern"
value
=
"%-5p: %c - %m%n"
/>
</
layout
>
</
appender
>
<!-- 3rdparty Loggers -->
<
logger
name
=
"org.springframework.core"
>
<
level
value
=
"info"
/>
</
logger
>
<
logger
name
=
"org.springframework.beans"
>
<
level
value
=
"info"
/>
</
logger
>
<
logger
name
=
"org.springframework.context"
>
<
level
value
=
"info"
/>
</
logger
>
<
logger
name
=
"org.springframework.web"
>
<
level
value
=
"info"
/>
</
logger
>
<!-- Root Logger -->
<
root
>
<
priority
value
=
"warn"
/>
<
appender-ref
ref
=
"console"
/>
</
root
>
</
log4j:configuration
>
|
整体项目结构
2.业务对象(Model Class)
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
|
package
com.journaldev.spring.jdbc.model;
public
class
Employee {
private
int
id;
private
String name;
private
String role;
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 getRole() {
return
role;
}
public
void
setRole(String role) {
this
.role = role;
}
@Override
public
String toString(){
return
"{ID="
+id+
",Name="
+name+
",Role="
+role+
"}"
;
}}
|
3.DAO Interface
包含基本的CRUD操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
package
com.journaldev.spring.jdbc.dao;
import
java.util.List;
import
com.journaldev.spring.jdbc.model.Employee;
//CRUD operations
public
interface
EmployeeDAO {
//Create
public
void
save(Employee employee);
//Read
public
Employee getById(
int
id);
//Update
public
void
update(Employee employee);
//Delete
public
void
deleteById(
int
id);
//Get All
public
List<Employee> getAll();
}
|
4.DAO Interface实现
4.1(原生态方式)
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
|
package
com.journaldev.spring.jdbc.dao;
import
java.sql.Connection;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.util.ArrayList;
import
java.util.List;
import
javax.sql.DataSource;
import
com.journaldev.spring.jdbc.model.Employee;
public
class
EmployeeDAOImpl
implements
EmployeeDAO {
private
DataSource dataSource;
public
void
setDataSource(DataSource dataSource) {
this
.dataSource = dataSource;
}
@Override
public
void
save(Employee employee) {
String query =
"insert into Employee (id, name, role) values (?,?,?)"
;
Connection con =
null
;
PreparedStatement ps =
null
;
try
{
con = dataSource.getConnection();
ps = con.prepareStatement(query);
ps.setInt(
1
, employee.getId());
ps.setString(
2
, employee.getName());
ps.setString(
3
, employee.getRole());
int
out = ps.executeUpdate();
if
(out !=
0
){
System.out.println(
"Employee saved with id="
+employee.getId());
}
else
System.out.println(
"Employee save failed with id="
+employee.getId());
}
catch
(SQLException e){
e.printStackTrace();
}
finally
{
try
{
ps.close();
con.close();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
}
@Override
public
Employee getById(
int
id) {
String query =
"select name, role from Employee where id = ?"
;
Employee emp =
null
;
Connection con =
null
;
PreparedStatement ps =
null
;
ResultSet rs =
null
;
try
{
con = dataSource.getConnection();
ps = con.prepareStatement(query);
ps.setInt(
1
, id);
rs = ps.executeQuery();
if
(rs.next()){
emp =
new
Employee();
emp.setId(id);
emp.setName(rs.getString(
"name"
));
emp.setRole(rs.getString(
"role"
));
System.out.println(
"Employee Found::"
+emp);
}
else
{
System.out.println(
"No Employee found with id="
+id);
}
}
catch
(SQLException e){
e.printStackTrace();
}
finally
{
try
{
rs.close();
ps.close();
con.close();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
return
emp;
}
@Override
public
void
update(Employee employee) {
String query =
"update Employee set name=?, role=? where id=?"
;
Connection con =
null
;
PreparedStatement ps =
null
;
try
{
con = dataSource.getConnection();
ps = con.prepareStatement(query);
ps.setString(
1
, employee.getName());
ps.setString(
2
, employee.getRole());
ps.setInt(
3
, employee.getId());
int
out = ps.executeUpdate();
if
(out !=
0
){
System.out.println(
"Employee updated with id="
+employee.getId());
}
else
System.out.println(
"No Employee found with id="
+employee.getId());
}
catch
(SQLException e){
e.printStackTrace();
}
finally
{
try
{
ps.close();
con.close();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
}
@Override
public
void
deleteById(
int
id) {
String query =
"delete from Employee where id=?"
;
Connection con =
null
;
PreparedStatement ps =
null
;
try
{
con = dataSource.getConnection();
ps = con.prepareStatement(query);
ps.setInt(
1
, id);
int
out = ps.executeUpdate();
if
(out !=
0
){
System.out.println(
"Employee deleted with id="
+id);
}
else
System.out.println(
"No Employee found with id="
+id);
}
catch
(SQLException e){
e.printStackTrace();
}
finally
{
try
{
ps.close();
con.close();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
}
@Override
public
List<Employee> getAll() {
String query =
"select id, name, role from Employee"
;
List<Employee> empList =
new
ArrayList<Employee>();
Connection con =
null
;
PreparedStatement ps =
null
;
ResultSet rs =
null
;
try
{
con = dataSource.getConnection();
ps = con.prepareStatement(query);
rs = ps.executeQuery();
while
(rs.next()){
Employee emp =
new
Employee();
emp.setId(rs.getInt(
"id"
));
emp.setName(rs.getString(
"name"
));
emp.setRole(rs.getString(
"role"
));
empList.add(emp);
}
}
catch
(SQLException e){
e.printStackTrace();
}
finally
{
try
{
rs.close();
ps.close();
con.close();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
return
empList;
}
}
|
4.2(JdbcTemplate方式)
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
|
package
com.journaldev.spring.jdbc.dao;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.util.ArrayList;
import
java.util.List;
import
java.util.Map;
import
javax.sql.DataSource;
import
org.springframework.jdbc.core.JdbcTemplate;
import
org.springframework.jdbc.core.RowMapper;
import
com.journaldev.spring.jdbc.model.Employee;
public
class
EmployeeDAOJDBCTemplateImpl
implements
EmployeeDAO {
private
DataSource dataSource;
public
void
setDataSource(DataSource dataSource) {
this
.dataSource = dataSource;
}
@Override
public
void
save(Employee employee) {
String query =
"insert into Employee (id, name, role) values (?,?,?)"
;
JdbcTemplate jdbcTemplate =
new
JdbcTemplate(dataSource);
Object[] args =
new
Object[] {employee.getId(), employee.getName(), employee.getRole()};
int
out = jdbcTemplate.update(query, args);
if
(out !=
0
){
System.out.println(
"Employee saved with id="
+employee.getId());
}
else
{
System.out.println(
"Employee save failed with id="
+employee.getId());
}
}
@Override
public
Employee getById(
int
id) {
String query =
"select id, name, role from Employee where id = ?"
;
JdbcTemplate jdbcTemplate =
new
JdbcTemplate(dataSource);
//using RowMapper anonymous class, we can create a separate RowMapper for reuse
Employee emp = jdbcTemplate.queryForObject(query,
new
Object[]{id},
new
RowMapper<Employee>(){
@Override
public
Employee mapRow(ResultSet rs,
int
rowNum)
throws
SQLException {
Employee emp =
new
Employee();
emp.setId(rs.getInt(
"id"
));
emp.setName(rs.getString(
"name"
));
emp.setRole(rs.getString(
"role"
));
return
emp;
}});
return
emp;
}
@Override
public
void
update(Employee employee) {
String query =
"update Employee set name=?, role=? where id=?"
;
JdbcTemplate jdbcTemplate =
new
JdbcTemplate(dataSource);
Object[] args =
new
Object[] {employee.getName(), employee.getRole(), employee.getId()};
int
out = jdbcTemplate.update(query, args);
if
(out !=
0
){
System.out.println(
"Employee updated with id="
+employee.getId());
}
else
{
System.out.println(
"No Employee found with id="
+employee.getId());
}
}
@Override
public
void
deleteById(
int
id) {
String query =
"delete from Employee where id=?"
;
JdbcTemplate jdbcTemplate =
new
JdbcTemplate(dataSource);
int
out = jdbcTemplate.update(query, id);
if
(out !=
0
){
System.out.println(
"Employee deleted with id="
+id);
}
else
{
System.out.println(
"No Employee found with id="
+id);
}
}
@Override
public
List<Employee> getAll() {
String query =
"select id, name, role from Employee"
;
JdbcTemplate jdbcTemplate =
new
JdbcTemplate(dataSource);
List<Employee> empList =
new
ArrayList<Employee>();
List<Map<String,Object>> empRows = jdbcTemplate.queryForList(query);
for
(Map<String,Object> empRow : empRows){
Employee emp =
new
Employee();
emp.setId(Integer.parseInt(String.valueOf(empRow.get(
"id"
))));
emp.setName(String.valueOf(empRow.get(
"name"
)));
emp.setRole(String.valueOf(empRow.get(
"role"
)));
empList.add(emp);
}
return
empList;
}
}
|
两个实现类,实现的功能是一样的。但通过对比,可以很容易发现。使用了JdbcTemplate ,可以帮助我们屏蔽底层的PreparedStatement ,ResultSet等对象的操作,简化了一些无聊的底层操作。
需要说明的是:mysql 默认的隔离级别是REPEATABLE-READ 。为什么没有执行提交操作,也会影响数据库呢?有兴趣的可以做实验确认下,想想为什么!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> select @
@tx_isolation
;
+-----------------+
| @
@tx_isolation
|
+-----------------+
| mysql> select @
@tx_isolation
-> ;
+-----------------+
| @
@tx_isolation
|
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row
in
set |
+-----------------+
1 row
in
set
|
5.测试例子
5.1 Main
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
|
package
com.journaldev.spring.jdbc;
import
java.util.List;
import
java.util.Random;
import
org.springframework.context.support.ClassPathXmlApplicationContext;
import
com.journaldev.spring.jdbc.dao.EmployeeDAO;
import
com.journaldev.spring.jdbc.model.Employee;
public
class
SpringMain {
public
static
void
main(String[] args) {
//Get the Spring Context
ClassPathXmlApplicationContext ctx =
new
ClassPathXmlApplicationContext(
"spring.xml"
);
//Get the EmployeeDAO Bean
EmployeeDAO employeeDAO = ctx.getBean(
"employeeDAO"
, EmployeeDAO.
class
);
//Run some tests for JDBC CRUD operations
Employee emp =
new
Employee();
int
rand =
new
Random().nextInt(
1000
);
emp.setId(rand);
emp.setName(
"Pankaj"
);
emp.setRole(
"Java Developer"
);
//Create
employeeDAO.save(emp);
//Read
Employee emp1 = employeeDAO.getById(rand);
System.out.println(
"Employee Retrieved::"
+emp1);
//Update
emp.setRole(
"CEO"
);
employeeDAO.update(emp);
//Get All
List<Employee> empList = employeeDAO.getAll();
System.out.println(empList);
//Delete
employeeDAO.deleteById(rand);
//Close Spring Context
ctx.close();
System.out.println(
"DONE"
);
}
}
|
5.2 spring junit集成版
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
|
import
com.journaldev.spring.jdbc.dao.EmployeeDAO;
import
com.journaldev.spring.jdbc.model.Employee;
import
org.junit.Test;
import
org.junit.runner.RunWith;
import
org.springframework.test.context.ContextConfiguration;
import
org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import
javax.annotation.Resource;
import
java.util.Random;
/**
* Created by Administrator on 2016/6/21.
*/
@RunWith
(SpringJUnit4ClassRunner.
class
)
@ContextConfiguration
(locations =
"classpath:spring.xml"
)
public
class
SpringDaoTest {
@Resource
private
EmployeeDAO employeeTemplateDAO;
@Resource
private
EmployeeDAO employeeDAO;
@Test
public
void
testSave(){
Employee emp =
new
Employee();
int
rand =
new
Random().nextInt(
1000
);
emp.setId(rand);
emp.setName(
"Pankaj"
);
emp.setRole(
"Java Developer"
);
employeeDAO.save(emp);
}
@Test
public
void
testSave2(){
Employee emp =
new
Employee();
int
rand =
new
Random().nextInt(
1000
);
emp.setId(rand);
emp.setName(
"Pankaj-2"
);
emp.setRole(
"Java Developer-2"
);
employeeTemplateDAO.save(emp);
}
}
|
通过2个测试代码的比较,相信很容易发现两者优缺点
版本 | 优点 | 缺点 |
Main | 简单上手,随时随地 | 编写代码多,没办法自动化 |
spring junit | 方便测试 管理资源对象方便 | 配置环境稍微有点麻烦) |
接下来,即将进入Spring Data的内容了。
附件为源码包
本文出自 “简单” 博客,请务必保留此出处http://dba10g.blog.51cto.com/764602/1791528