1.准备工作
-
数据库中建一个测试用的表
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(256) CHARACTER SET utf8mb4 DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;
-
创建一个User实体类
public class User {
private String name;
private Integer age;
//getter and setter..
//toString..
2. Java配置
import javax.sql.DataSource;
import ...
@Configuration
public class MainConfig{
@Bean
public DataSource dataSource(){
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUsername("root");
dataSource.setPassword("root");
dataSource.setUrl("jdbc:mysql:///test");
return dataSource;
}
@Bean
public JdbcTemplate jdbcTemplate(){
return new JdbcTemplate(dataSource());
}
}
3.测试
public class Test {
private ApplicationContext ioc;
private JdbcTemplate template;
@Before
public void getContext(){
this.ioc = new AnnotationConfigApplicationContext(MainConfig.class);
this.template = ioc.getBean(JdbcTemplate.class);
}
@org.junit.Test
public void testJdbcTemplate(){
String insertSql = "insert into users(name, age) values(?,?)";
// template.update(insertSql, "jerry", 20);
String updateSql = "update users set age=? where name=?";
// template.update(updateSql, 38, "jerry");
String deleteSql = "delete from users where age = ?";
// template.update(deleteSql, 1);
//单查询
String selectSql = "select * from users where name=?";
User user = template.queryForObject(selectSql, new BeanPropertyRowMapper<>(User.class), "rose");
System.out.println(user);
}
/**
* BeanPropertyRowMapper要求数据库字段和Bean属性名一一对应
* 可以通过取别名和自定义RowMapper解决字段与属性名不对应的问题
* 取别名方式:select user_name as name , age from users
* 下面介绍自定义RowMapper
*/
@org.junit.Test
public void testCustom(){
String selectSql = "select * from users where name=?";
List<User> users = template.query(selectSql, new RowMapper<User>() {
/**
* @param resultSet 查询所得的结果集,其中某一行的结果
* @param i 当前所在行的下标
* @return 封装后的对象
* @throws SQLException
*/
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {
System.out.println(i);
User user = new User();
user.setName(resultSet.getString("name"));
user.setAge(resultSet.getInt("age"));
return user;
}
}, "jerry");
users.forEach(System.out::println);
}
//批量查询
@org.junit.Test
public void testBatchQuery(){
String selectSql = "select * from users";
List<User> users = template.query(selectSql, new BeanPropertyRowMapper<>(User.class));
users.forEach(System.out::println);
}
}
4. 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="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="url" value="jdbc:mysql:///test"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
测试代码只需使用ClassPathXmlApplicationContext("xml path") 创建容器即可。