1、首先导入包:
2、然后建立数据库表
CREATE DATABASE day36;
USE day36;
CREATE TABLE t_user(
id INT PRIMARY KEY,
NAME VARCHAR(20),
gender CHAR(2)
);
3、在编写实体
package
star.july.entity;
public
class
User {
private
int
id
;
private
String
name
;
private
String
gender
;
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 getGender() {
return
gender
;
}
public
void
setGender(String gender) {
this
.
gender
= gender;
}
@Override
public
String toString() {
return
"User [id="
+
id
+
", name="
+
name
+
", gender="
+
gender
+
"]"
;
}
}
4、编写dao类
package
star
.
july
.
dao
;
import
java
.
util
.
List
;
import
star
.
july
.
entity
.
User
;
public
interface
IUserDao
{
public
void
save
(
User user
);
public
void
update
(
User user
);
public
void
delete
(
int
id
);
public
List
<
User
>
queryAll
();
public
List
<
User
>
queryPages
(
int
curPage
,
int
PageSize
);
public
User queryById
(
int
id
);
public
int
queryCount
();
}
dao类实现接口
package
star
.
july
.
dao
;
import
java
.
sql
.
ResultSet
;
import
java
.
sql
.
SQLException
;
import
java
.
util
.
List
;
import
org
.
springframework
.
jdbc
.
core
.
JdbcTemplate
;
import
org
.
springframework
.
jdbc
.
core
.
RowMapper
;
import
star
.
july
.
entity
.
User
;
public
class
UserDaoImpl
implements
IUserDao
{
//
用于接收
jdbcTemplate
对象
private
JdbcTemplate jdbcTemplate
;
public
void
setJdbcTemplate
(
JdbcTemplate jdbcTemplate
)
{
this
.
jdbcTemplate
=
jdbcTemplate
;
}
//
添加
public
void
save
(
User user
)
{
jdbcTemplate
.
update
(
"insert into t_user(id,name,gender) values(?,?,?)"
,
user
.
getId
(),
user
.
getName
(),
user
.
getGender
()
);
}
//
修改
public
void
update
(
User user
)
{
jdbcTemplate
.
update
(
"update t_user set name=?,gender=? where id=?"
,
user
.
getName
(),
user
.
getGender
(),
user
.
getId
()
);
}
//
删除
public
void
delete
(
int
id
)
{
jdbcTemplate
.
update
(
"delete from t_user where id=?"
,
id
);
}
//
查询所有数据
public
List
<
User
>
queryAll
()
{
return
jdbcTemplate
.
query
(
"select * from t_user"
,
new
RowMapper
(
)
{
public
Object mapRow
(
ResultSet rs
,
int
index
)
throws
SQLException
{
User u
=
new
User
();
u
.
setId
(
rs
.
getInt
(
"id"
));
u
.
setName
(
rs
.
getString
(
"name"
));
u
.
setGender
(
rs
.
getString
(
"gender"
));
return
u
;
}
}
);
}
//
分页查询
public
List
<
User
>
queryPages
(
int
curPage
,
int
pageSize
)
{
return
(
List
<
User
>)
jdbcTemplate
.
query
(
"select * from t_user limit ? , ?"
,
new
RowMapper
(){
public
Object mapRow
(
ResultSet rs
,
int
arg1
)
throws
SQLException
{
User u
=
new
User
();
u
.
setId
(
rs
.
getInt
(
"id"
));
u
.
setName
(
rs
.
getString
(
"name"
));
u
.
setGender
(
rs
.
getString
(
"gender"
));
return
u
;
}
},(
curPage
-
1
)*
pageSize
,
pageSize
);
}
public
User queryById
(
int
id
)
{
return
jdbcTemplate
.
queryForObject
(
"select * from t_user where id = ?"
,
new
RowMapper
(){
public
Object mapRow
(
ResultSet rs
,
int
index
)
throws
SQLException
{
User u
=
new
User
();
u
.
setId
(
rs
.
getInt
(
"id"
));
u
.
setName
(
rs
.
getString
(
"name"
));
u
.
setGender
(
rs
.
getString
(
"gender"
));
return
u
;
}
},
id
);
}
//
查询总计录数
public
int
queryCount
()
{
return
jdbcTemplate
.
queryForObject
(
"select count(*) from t_user"
,
Long
.
class
).
intValue
();
}
}
5、编写applicationContext.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
http://www.springframework.org/schema/beans/spring-beans.xsd"
>
<!-- 1、创建连接池对象 -->
<
bean
id
=
"dataSourceID"
class
=
"com.mchange.v2.c3p0.ComboPooledDataSource"
>
<!-- 注入参数 -->
<
property
name
=
"jdbcUrl"
value
=
"jdbc:mysql://localhost:3306/day36"
></
property
>
<
property
name
=
"driverClass"
value
=
"com.mysql.jdbc.Driver"
></
property
>
<
property
name
=
"user"
value
=
"root"
></
property
>
<
property
name
=
"password"
value
=
"root"
></
property
>
</
bean
>
<!-- 2、创建spring提供的JdbcTemplate模块对象 -->
<
bean
id
=
"jdbcTemplateID"
class
=
"org.springframework.jdbc.core.JdbcTemplate"
>
<!-- 注入连接池对象 -->
<
property
name
=
"dataSource"
ref
=
"dataSourceID"
></
property
>
</
bean
>
<!-- 3、创建dao对象 -->
<
bean
id
=
"userDaoID"
class
=
"star.july.dao.UserDaoImpl"
>
<!-- 注入jdbcTemplate对象 -->
<
property
name
=
"jdbcTemplate"
ref
=
"jdbcTemplateID"
></
property
>
</
bean
>
</
beans
>
6、测试
package
star
.
july
.
test
;
import
java
.
util
.
List
;
import
org
.
junit
.
Test
;
import
org
.
springframework
.
context
.
ApplicationContext
;
import
org
.
springframework
.
context
.
support
.
ClassPathXmlApplicationContext
;
import
star
.
july
.
dao
.
IUserDao
;
import
star
.
july
.
dao
.
UserDaoImpl
;
import
star
.
july
.
entity
.
User
;
public
class
Demo
{
//
添加
@Test
public
void
test
(){
ApplicationContext ac
=
new
ClassPathXmlApplicationContext
(
"/applicationContext.xml"
);
IUserDao userDao
=
(
IUserDao
)
ac
.
getBean
(
"userDaoID"
);
User u
=
new
User
();
u
.
setId
(
3
);
u
.
setName
(
"
徐渭熊
"
);
u
.
setGender
(
"
女
"
);
userDao
.
save
(
u
);
}
//
修改
@Test
public
void
test2
(){
ApplicationContext ac
=
new
ClassPathXmlApplicationContext
(
"/applicationContext.xml"
);
IUserDao userDao
=
(
IUserDao
)
ac
.
getBean
(
"userDaoID"
);
User u
=
new
User
();
u
.
setId
(
1
);
u
.
setName
(
"
徐奇
"
);
u
.
setGender
(
"
男
"
);
userDao
.
update
(
u
);
}
//
删除
@Test
public
void
test3
(){
ApplicationContext ac
=
new
ClassPathXmlApplicationContext
(
"/applicationContext.xml"
);
IUserDao userDao
=
(
IUserDao
)
ac
.
getBean
(
"userDaoID"
);
userDao
.
delete
(
2
);
}
//
查询所有对象
@Test
public
void
test4
(){
ApplicationContext ac
=
new
ClassPathXmlApplicationContext
(
"/applicationContext.xml"
);
IUserDao userDao
=
(
IUserDao
)
ac
.
getBean
(
"userDaoID"
);
List
<
User
>
list
=
userDao
.
queryAll
();
for
(
User u
:
list
){
System
.
out
.
println
(
u
);
}
}
//
根据
id
查找对象
@Test
public
void
test5
(){
ApplicationContext ac
=
new
ClassPathXmlApplicationContext
(
"/applicationContext.xml"
);
IUserDao userDao
=
(
IUserDao
)
ac
.
getBean
(
"userDaoID"
);
User u
=
userDao
.
queryById
(
2
);
System
.
out
.
println
(
u
);
}
//
查找总计录数
@Test
public
void
test6
(){
ApplicationContext ac
=
new
ClassPathXmlApplicationContext
(
"/applicationContext.xml"
);
IUserDao userDao
=
(
IUserDao
)
ac
.
getBean
(
"userDaoID"
);
int
count
=
userDao
.
queryCount
();
System
.
out
.
println
(
count
);
}
//
分页查询
@Test
public
void
test7
(){
ApplicationContext ac
=
new
ClassPathXmlApplicationContext
(
"/applicationContext.xml"
);
IUserDao userDao
=
(
IUserDao
)
ac
.
getBean
(
"userDaoID"
);
List
<
User
>
list
=
userDao
.
queryPages
(
2
,
2
);
for
(
User user
:
list
){
System
.
out
.
println
(
user
);
}
}
}
抽取数据可的参数到properties文件,首先要配置context命名空间(红色部分)
再配置全局读取: <context:property-placeholder location="classpath:db.properties"/>
最后用表达式获取值(黑色加粗部分)
<?
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:context="http://www.springframework.org/schema/context"
xsi:schemaLocation
=
"http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd"
>
<
context:property-placeholder
location
=
"classpath:db.properties"
/>
<!-- 1、创建连接池对象 -->
<
bean
id
=
"dataSourceID"
class
=
"com.mchange.v2.c3p0.ComboPooledDataSource"
>
<!-- 注入参数 -->
<
property
name
=
"jdbcUrl"
value
=
"${jdbcUrl}"
></
property
>
<
property
name
=
"driverClass"
value
=
"${driverClass}"
></
property
>
<
property
name
=
"user"
value
=
"${user}"
></
property
>
<
property
name
=
"password"
value
=
"${password}"
></
property
>
</
bean
>
<!-- 2、创建spring提供的JdbcTemplate模块对象 -->
<
bean
id
=
"jdbcTemplateID"
class
=
"org.springframework.jdbc.core.JdbcTemplate"
>
<!-- 注入连接池对象 -->
<
property
name
=
"dataSource"
ref
=
"dataSourceID"
></
property
>
</
bean
>
<!-- 3、创建dao对象 -->
<
bean
id
=
"userDaoID"
class
=
"star.july.dao.UserDaoImpl"
>
<!-- 注入jdbcTemplate对象 -->
<
property
name
=
"jdbcTemplate"
ref
=
"jdbcTemplateID"
></
property
>
</
bean
>
</
beans
>