Spring对jdbc原始API对象的简单封装
基本使用
pom文件引入mysql-connector-java、spring-jdbc、c3p0、junit、lombok依赖
<?xml version="1.0" encoding="UTF-8"?>
<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.example</groupId>
<artifactId>SpringMvcModule</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.8</version>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
数据库users表
main的jave的com.kdy.domain中User
@Data//包括get、set、equals、hashCode、canEqual、toString、无参构造,没有有参构造
@NoArgsConstructor
@AllArgsConstructor
public class User {
private Integer id;
private String name;
private String email;
private Date birthday;
private Integer infoId;
private String englishTeacher;
}
test的java里com.kdu.test中JdbcTemplateTest
public class JdbcTemplateTest {
@Test
public void test1() throws PropertyVetoException, ParseException {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/jdbc");
dataSource.setUser("root");
dataSource.setPassword("root123");
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource);
String dateStr ="2021-02-22" ;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date birthday = sdf.parse(dateStr);
int count = jdbcTemplate.update("insert into users(name,password,email,birthday,infoId,english_teacher) values (?,?,?,?,?,?)",
"tom666","hello666","email666",birthday, 6,"eng666");
System.out.println(count);
}
}
运行上面test1方法即可。
Spring接管JdbcTemplate对象
xml文件配置的方式
pom文件引入spring-context依赖
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.0.5.RELEASE</version>
</dependency>
resource下new->XML configuration file->spring config,名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">
<!--数据源-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql:///jdbc"></property>
<property name="user" value="root"></property>
<property name="password" value="root123"></property>
</bean>
<!--JdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
test的java里com.kdu.test中JdbcTemplateTest
public class JdbcTemplateTest {
@Test
public void test1() throws PropertyVetoException, ParseException {
ClassPathXmlApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);//根据类型获取spring容器中的bean
String dateStr ="2021-02-22" ;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date birthday = sdf.parse(dateStr);
int count = jdbcTemplate.update("insert into users(name,password,email,birthday,infoId,english_teacher) values (?,?,?,?,?,?)",
"tom666","hello666","email666",birthday, 6,"eng666");
System.out.println(count);
}
}
applicationContext.xml中数据源抽取jdbc.properties文件
resource下创建jdbc.peroperties文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.username=root
jdbc.password=root123
resource目录下的applicationContext.xml引入jdbc.properties文件,并el表达式引入配置
<?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:jdbc.properties"/><!--引入配置文件,使用el获取-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"></property>
<property name="jdbcUrl" value="${jdbc.url}"></property>
<property name="user" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<!--JdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
运行测试文件的test1方法即可
public class JdbcTemplateTest {
@Test
public void test1() throws PropertyVetoException, ParseException {
ClassPathXmlApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);//根据类型获取spring容器中的bean
String dateStr ="2021-02-22" ;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date birthday = sdf.parse(dateStr);
int count = jdbcTemplate.update("insert into users(name,password,email,birthday,infoId,english_teacher) values (?,?,?,?,?,?)",
"tom666","hello666","email666",birthday, 6,"eng666");
System.out.println(count);
}
}
测试文件使用Spring集成junit的spring-test
pom文件除了junit外还要引入spring-test依赖
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.0.5.RELEASE</version>
</dependency>
test的java里com.kdu.test中JdbcTemplateTest
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateTest {
@Autowired//先根据类型后根据名称注入
private JdbcTemplate jdbcTemplate;//根据类型注入spring容器的某个bean
@Test
public void test1() throws PropertyVetoException, ParseException {
String dateStr ="2021-02-22" ;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date birthday = sdf.parse(dateStr);
int count = jdbcTemplate.update("insert into users(name,password,email,birthday,infoId,english_teacher) values (?,?,?,?,?,?)",
"tom666","hello666","email666",birthday, 6,"eng666");
System.out.println(count);
}
}
jdbcTemplate的增删改查的api
test的java里com.kdu.test中JdbcTemplateTest
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateTest {
@Autowired//先根据类型后根据名称注入
private JdbcTemplate jdbcTemplate;//根据类型注入spring容器的某个bean
@Test
public void testAdd() throws ParseException {
String dateStr ="2021-02-22" ;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date birthday = sdf.parse(dateStr);
int count = jdbcTemplate.update("insert into users(name,password,email,birthday,infoId,english_teacher) values (?,?,?,?,?,?)",
"tom666","hello666","email666",birthday, 6,"eng666");
System.out.println("add"+count);
}
@Test
public void testDelete(){
int count = jdbcTemplate.update("delete from users where id = ?",41);
System.out.println("delete"+count);
}
@Test
public void testUpdate(){
int count = jdbcTemplate.update("update users set email = ? where id = ?" ,"emailUpdate666",42);
System.out.println("update"+count);
}
/*查询所有*/
@Test
public void testQueryAll(){
List<User> userList = jdbcTemplate.query("select * from users", new BeanPropertyRowMapper<User>(User.class));
System.out.println(userList);
}
/*查询一个*/
@Test
public void testQueryOne(){
User user = jdbcTemplate.queryForObject("select * from users where id = ?", new BeanPropertyRowMapper<User>(User.class),10);
System.out.println(user);
}
/*查询聚合函数结果*/
@Test
public void testQueryCount(){
Long count = jdbcTemplate.queryForObject("select count(*) from users", Long.class);
System.out.println(count);
}
}
jdbcTemplate的新增操作的主键返回
/*
* 主键返回
* */
@Override
public Long save(User user) {
//想要新增操作返回主键,不能使用下面这个新增操作的api
//jdbcTemplate.update("insert into sys_user(name,password,email,birthday,infoId,english_teacher) values(?,?,?,?,?,?)", user.getName(), user.getPassword(), user.getEmail(), user.getBirthday(), user.getInfoId(), user.getEnglishTeacher());、
String sql = "insert into sys_user(name,password,email,birthday,infoId,english_teacher) values(?,?,?,?,?,?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getName());
ps.setString(2, user.getPassword());
ps.setString(3, user.getEmail());
ps.setDate(4, new java.sql.Date(user.getBirthday().getTime()));
ps.setInt(5, user.getInfoId());
ps.setString(6, user.getEnglishTeacher());
return ps;
}
}, keyHolder);
Long generatedId = keyHolder.getKey().longValue();
return generatedId;
}