1
2
3
4
5
6
7
8
9
|
DROP
TABLE
IF EXISTS `blog`;
CREATE
TABLE
`blog` (
`id`
int
(10)
NOT
NULL
auto_increment,
`title`
varchar
(200)
NOT
NULL
,
`
date
`
varchar
(50)
NOT
NULL
,
`authername`
varchar
(15)
NOT
NULL
,
`content`
varchar
(500)
NOT
NULL
,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8;
|
1
2
3
4
5
6
7
8
|
DROP
TABLE
IF EXISTS `
user
`;
CREATE
TABLE
`
user
` (
`id`
int
(11)
NOT
NULL
auto_increment,
`userName`
varchar
(50)
default
NULL
,
`userAge`
int
(11)
default
NULL
,
`userAddress`
varchar
(200)
default
NULL
,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8;
|
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
|
package
com.whut.inter;
import
java.util.List;
import
org.apache.ibatis.annotations.Delete;
import
org.apache.ibatis.annotations.Insert;
import
org.apache.ibatis.annotations.Param;
import
org.apache.ibatis.annotations.Select;
import
org.apache.ibatis.annotations.Update;
import
com.whut.model.User;
//最基本的注解CRUD
public
interface
IUserDAO {
@Select
(
"select *from User"
)
public
List<User> retrieveAllUsers();
//注意这里只有一个参数,则#{}中的标识符可以任意取
@Select
(
"select *from User where id=#{idss}"
)
public
User retrieveUserById(
int
id);
@Select
(
"select *from User where id=#{id} and userName like #{name}"
)
public
User retrieveUserByIdAndName(
@Param
(
"id"
)
int
id,
@Param
(
"name"
)String names);
@Insert
(
"INSERT INTO user(userName,userAge,userAddress) VALUES(#{userName},"
+
"#{userAge},#{userAddress})"
)
public
void
addNewUser(User user);
@Delete
(
"delete from user where id=#{id}"
)
public
void
deleteUser(
int
id);
@Update
(
"update user set userName=#{userName},userAddress=#{userAddress}"
+
" where id=#{id}"
)
public
void
updateUser(User user);
}
|
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
|
package
com.whut.test;
import
java.io.IOException;
import
java.io.Reader;
import
java.util.List;
import
org.apache.ibatis.io.Resources;
import
org.apache.ibatis.session.SqlSession;
import
org.apache.ibatis.session.SqlSessionFactory;
import
org.apache.ibatis.session.SqlSessionFactoryBuilder;
import
com.whut.inter.IUserDAO;
import
com.whut.model.User;
public
class
TestUser {
private
static
SqlSessionFactory sqlSessionFactory;
private
static
Reader reader;
static
{
try
{
Reader reader = Resources.getResourceAsReader(
"mybatic-config.xml"
);
sqlSessionFactory =
new
SqlSessionFactoryBuilder().build(reader);
// 由于使用了注解,所以在主配置文件没有mapper,需要在代码里显示注册该mapper接口
sqlSessionFactory.getConfiguration().addMapper(IUserDAO.
class
);
}
catch
(IOException e) {
e.printStackTrace();
}
}
public
static
void
main(String[] args) {
// testSelectAll();
// testSelectByConditions();
//testAddUser();
//testDeleteUser();
testUpateUser();
}
public
static
void
testSelectAll() {
// sqlSessionFactory.getConfiguration().addMapper(IUserDAO.class);
SqlSession session = sqlSessionFactory.openSession();
try
{
IUserDAO userDAO = session.getMapper(IUserDAO.
class
);
List<User> users = userDAO.retrieveAllUsers();
System.out.println(
"用户编号\t"
+
"姓名\t"
+
"年龄\t住址"
);
for
(User u : users) {
System.out.println(u.getId() +
"\t"
+ u.getUserName() +
"\t"
+ u.getUserAge() +
"\t"
+ u.getUserAddress());
}
}
finally
{
session.close();
}
}
public
static
void
testSelectByConditions() {
SqlSession session = sqlSessionFactory.openSession();
try
{
IUserDAO userDAO = session.getMapper(IUserDAO.
class
);
User u = userDAO.retrieveUserByIdAndName(
4
,
"%spring%"
);
if
(u !=
null
) {
System.out.println(
"用户编号\t"
+
"姓名\t"
+
"年龄\t住址"
);
System.out.println(u.getId() +
"\t"
+ u.getUserName() +
"\t"
+ u.getUserAge() +
"\t"
+ u.getUserAddress());
}
}
finally
{
session.close();
}
}
public
static
void
testAddUser() {
User u =
new
User();
u.setUserName(
"dongtian"
);
u.setUserAge(
51
);
u.setUserAddress(
"hubeisheng"
);
SqlSession session = sqlSessionFactory.openSession();
try
{
IUserDAO userDAO = session.getMapper(IUserDAO.
class
);
userDAO.addNewUser(u);
session.commit();
}
finally
{
session.close();
}
}
public
static
void
testDeleteUser() {
SqlSession session = sqlSessionFactory.openSession();
try
{
IUserDAO userDAO = session.getMapper(IUserDAO.
class
);
userDAO.deleteUser(
7
);
session.commit();
}
finally
{
session.close();
}
}
public
static
void
testUpateUser() {
User u =
new
User();
u.setId(
4
);
u.setUserName(
"dongtian"
);
u.setUserAge(
51
);
u.setUserAddress(
"hubeisheng"
);
SqlSession session = sqlSessionFactory.openSession();
try
{
IUserDAO userDAO = session.getMapper(IUserDAO.
class
);
userDAO.updateUser(u);
session.commit();
}
finally
{
session.close();
}
}
}
|
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
|
package
com.whut.inter;
import
java.util.List;
import
org.apache.ibatis.annotations.CacheNamespace;
import
org.apache.ibatis.annotations.DeleteProvider;
import
org.apache.ibatis.annotations.InsertProvider;
import
org.apache.ibatis.annotations.Options;
import
org.apache.ibatis.annotations.Param;
import
org.apache.ibatis.annotations.Result;
import
org.apache.ibatis.annotations.ResultMap;
import
org.apache.ibatis.annotations.Results;
import
org.apache.ibatis.annotations.SelectProvider;
import
org.apache.ibatis.annotations.UpdateProvider;
import
org.apache.ibatis.type.JdbcType;
import
com.whut.model.Blog;
import
com.whut.sqlTool.BlogSqlProvider;
@CacheNamespace
(size=
100
)
public
interface
IBlogDAO {
@SelectProvider
(type = BlogSqlProvider.
class
, method =
"getSql"
)
@Results
(value ={
@Result
(id=
true
, property=
"id"
,column=
"id"
,javaType=Integer.
class
,jdbcType=JdbcType.INTEGER),
@Result
(property=
"title"
,column=
"title"
,javaType=String.
class
,jdbcType=JdbcType.VARCHAR),
@Result
(property=
"date"
,column=
"date"
,javaType=String.
class
,jdbcType=JdbcType.VARCHAR),
@Result
(property=
"authername"
,column=
"authername"
,javaType=String.
class
,jdbcType=JdbcType.VARCHAR),
@Result
(property=
"content"
,column=
"content"
,javaType=String.
class
,jdbcType=JdbcType.VARCHAR),
})
public
Blog getBlog(
@Param
(
"id"
)
int
id);
@SelectProvider
(type = BlogSqlProvider.
class
, method =
"getAllSql"
)
@Results
(value ={
@Result
(id=
true
, property=
"id"
,column=
"id"
,javaType=Integer.
class
,jdbcType=JdbcType.INTEGER),
@Result
(property=
"title"
,column=
"title"
,javaType=String.
class
,jdbcType=JdbcType.VARCHAR),
@Result
(property=
"date"
,column=
"date"
,javaType=String.
class
,jdbcType=JdbcType.VARCHAR),
@Result
(property=
"authername"
,column=
"authername"
,javaType=String.
class
,jdbcType=JdbcType.VARCHAR),
@Result
(property=
"content"
,column=
"content"
,javaType=String.
class
,jdbcType=JdbcType.VARCHAR),
})
public
List<Blog> getAllBlog();
@SelectProvider
(type = BlogSqlProvider.
class
, method =
"getSqlByTitle"
)
@ResultMap
(value =
"sqlBlogsMap"
)
//这里调用resultMap,这个是SQL配置文件中的,必须该SQL配置文件与本接口有相同的全限定名
//注意文件中的namespace路径必须是使用@resultMap的类路径
public
List<Blog> getBlogByTitle(
@Param
(
"title"
)String title);
@InsertProvider
(type = BlogSqlProvider.
class
, method =
"insertSql"
)
public
void
insertBlog(Blog blog);
@UpdateProvider
(type = BlogSqlProvider.
class
, method =
"updateSql"
)
public
void
updateBlog(Blog blog);
@DeleteProvider
(type = BlogSqlProvider.
class
, method =
"deleteSql"
)
@Options
(useCache =
true
, flushCache =
false
, timeout =
10000
)
public
void
deleteBlog(
int
ids);
}
|
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
|
package
com.whut.sqlTool;
import
java.util.Map;
import
static
org.apache.ibatis.jdbc.SqlBuilder.*;
public
class
BlogSqlProvider {
private
final
static
String TABLE_NAME =
"blog"
;
public
String getSql(Map<Integer, Object> parameter) {
BEGIN();
//SELECT("id,title,authername,date,content");
SELECT(
"*"
);
FROM(TABLE_NAME);
//注意这里这种传递参数方式,#{}与map中的key对应,而map中的key又是注解param设置的
WHERE(
"id = #{id}"
);
return
SQL();
}
public
String getAllSql() {
BEGIN();
SELECT(
"*"
);
FROM(TABLE_NAME);
return
SQL();
}
public
String getSqlByTitle(Map<String, Object> parameter) {
String title = (String) parameter.get(
"title"
);
BEGIN();
SELECT(
"*"
);
FROM(TABLE_NAME);
if
(title !=
null
)
WHERE(
" title like #{title}"
);
return
SQL();
}
public
String insertSql() {
BEGIN();
INSERT_INTO(TABLE_NAME);
VALUES(
"title"
,
"#{title}"
);
// VALUES("title", "#{tt.title}");
//这里是传递一个Blog对象的,如果是利用上面tt.方式,则必须利用Param来设置别名
VALUES(
"date"
,
"#{date}"
);
VALUES(
"authername"
,
"#{authername}"
);
VALUES(
"content"
,
"#{content}"
);
return
SQL();
}
public
String deleteSql() {
BEGIN();
DELETE_FROM(TABLE_NAME);
WHERE(
"id = #{id}"
);
return
SQL();
}
public
String updateSql() {
BEGIN();
UPDATE(TABLE_NAME);
SET(
"content = #{content}"
);
WHERE(
"id = #{id}"
);
return
SQL();
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
|
<?
xml
version
=
"1.0"
encoding
=
"UTF-8"
?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<
mapper
namespace
=
"com.whut.inter.IBlogDAO"
>
<
resultMap
type
=
"Blog"
id
=
"sqlBlogsMap"
>
<
id
property
=
"id"
column
=
"id"
/>
<
result
property
=
"title"
column
=
"title"
/>
<
result
property
=
"authername"
column
=
"authername"
/>
<
result
property
=
"date"
column
=
"date"
/>
<
result
property
=
"content"
column
=
"content"
/>
</
resultMap
>
</
mapper
>
|
1
2
3
|
<
mappers
>
<
mapper
resource
=
"com/whut/model/Blog.xml"
/>
</
mappers
>
|
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
|
package
com.whut.test;
import
java.io.IOException;
import
java.io.Reader;
import
java.util.List;
import
org.apache.ibatis.io.Resources;
import
org.apache.ibatis.session.SqlSession;
import
org.apache.ibatis.session.SqlSessionFactory;
import
org.apache.ibatis.session.SqlSessionFactoryBuilder;
import
com.whut.inter.IBlogDAO;
import
com.whut.model.Blog;
/**
* 注意这里是一种利用SqlBuilder以及多种设置来注解使用
* @author zxl
*
*/
public
class
TestBlog {
private
static
SqlSessionFactory sqlSessionFactory;
private
static
Reader reader;
static
{
try
{
Reader reader = Resources.getResourceAsReader(
"mybatic-config.xml"
);
sqlSessionFactory =
new
SqlSessionFactoryBuilder().build(reader);
// 由于使用了注解,所以在主配置文件没有mapper,需要在代码里显示注册该mapper接口
//sqlSessionFactory.getConfiguration().addMapper(IBlogDAO.class);
}
catch
(IOException e) {
e.printStackTrace();
}
}
public
static
void
main(String[] args) {
//testOneBlog();
//testAllBlog();
//testBlogByTitle();
//testAddBlog();
//testDeleteBlog();
testUpdateBlog();
}
public
static
void
testOneBlog()
{
SqlSession session=sqlSessionFactory.openSession();
try
{
IBlogDAO dao=session.getMapper(IBlogDAO.
class
);
Blog blog=dao.getBlog(
2
);
System.out.println(blog.getId()+
"\t"
+blog.getAuthername()+
"\t"
+blog.getTitle());
}
finally
{
session.close();
}
}
public
static
void
testAllBlog()
{
SqlSession session=sqlSessionFactory.openSession();
try
{
IBlogDAO dao=session.getMapper(IBlogDAO.
class
);
List<Blog> blogs=dao.getAllBlog();
System.out.println(
"编号\t作者\t标题"
);
for
(Blog blog:blogs)
System.out.println(blog.getId()+
"\t"
+blog.getAuthername()+
"\t"
+blog.getTitle());
}
finally
{
session.close();
}
}
public
static
void
testBlogByTitle()
{
SqlSession session=sqlSessionFactory.openSession();
try
{
IBlogDAO dao=session.getMapper(IBlogDAO.
class
);
List<Blog> blogs=dao.getBlogByTitle(
"%word%"
);
System.out.println(
"编号\t作者\t标题"
);
for
(Blog blog:blogs)
System.out.println(blog.getId()+
"\t"
+blog.getAuthername()+
"\t"
+blog.getTitle());
}
finally
{
session.close();
}
}
public
static
void
testAddBlog()
{
SqlSession session=sqlSessionFactory.openSession();
Blog blog=
new
Blog();
blog.setTitle(
"chuntian"
);
blog.setAuthername(
"xiaohua"
);
blog.setDate(
"2013/12/25"
);
blog.setContent(
"bushuangyayya"
);
try
{
IBlogDAO dao=session.getMapper(IBlogDAO.
class
);
dao.insertBlog(blog);
session.commit();
}
finally
{
session.close();
}
}
public
static
void
testDeleteBlog()
{
SqlSession session=sqlSessionFactory.openSession();
try
{
IBlogDAO dao=session.getMapper(IBlogDAO.
class
);
dao.deleteBlog(
5
);
session.commit();
}
finally
{
session.close();
}
}
public
static
void
testUpdateBlog()
{
SqlSession session=sqlSessionFactory.openSession();
Blog blog=
new
Blog();
blog.setId(
6
);
blog.setTitle(
"daxuexiaoyuan"
);
blog.setAuthername(
"xiaohua"
);
blog.setDate(
"2013/2/25"
);
blog.setContent(
"冷死了"
);
try
{
IBlogDAO dao=session.getMapper(IBlogDAO.
class
);
dao.updateBlog(blog);
session.commit();
}
finally
{
session.close();
}
}
}
|