用JdbcTemplateTool配合JdbcTemplate实现更便捷的数据库操作
时间：2022-03-14 00:17
<dependency>
<groupId>org.crazycake</groupId>
<artifactId>jdbctemplatetool</artifactId>
<version>1.0.4-RELEASE</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>3.2.2.RELEASE</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.2.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.19</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>3.2.2.RELEASE</version>
<scope>test</scope>
</dependency>
CREATE USER 'travis'@'%' IDENTIFIED BY '';
GRANT ALL ON jtt_test.* TO 'travis'@'%';
flush privileges;
创建一张表 DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(300) NOT NULL,
`join_date` datetime NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `employee` */
insert into `employee`(`id`,`name`,`join_date`,`age`) values (1,'jack','2014-09-22 00:00:00',23),(2,'ted','2014-08-30 00:00:00',25),(3,'jim','2014-06-22 00:00:00',33);
<?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-3.0.xsd">
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="jdbcUrl"><value>jdbc:mysql://localhost:3306/jtt_test?characterEncoding=utf8</value></property>
<property name="driverClass"><value>com.mysql.jdbc.Driver</value></property>
<property name="user"><value>travis</value></property>
<property name="password"><value></value></property>
</bean>
<bean id = "jdbcTemplate" class = "org.springframework.jdbc.core.JdbcTemplate">
<property name = "dataSource" ref="dataSource"/>
</bean>
<bean id="jdbcTemplateTool" class="org.crazycake.jdbcTemplateTool.JdbcTemplateTool">
<property name = "jdbcTemplate" ref="jdbcTemplate" />
</bean>
</beans>
import java.sql.Timestamp;
import javax.persistence.Id;
public class Employee {
private Integer id;
private String name;
private Timestamp joinDate;
private Integer age;
@Id
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Timestamp getJoinDate() {
return joinDate;
}
public void setJoinDate(Timestamp joinDate) {
this.joinDate = joinDate;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;
import java.util.List;
import org.crazycake.jdbcTemplateTool.JdbcTemplateTool;
import org.junit.Test;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests;
@ContextConfiguration(locations={"classpath:spring.xml"})
public class HelloJTTTest extends AbstractJUnit4SpringContextTests{
@Test
public void testSave(){
JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class);
Employee e = new Employee();
e.setId(4);
e.setName("billy");
Date now = new Date();
e.setJoinDate(new Timestamp(now.getTime()));
e.setAge(33);
try {
jtt.save(e);
} catch (Exception e1) {
e1.printStackTrace();
}
}
}
@Test
public void testList(){
JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class);
List<Employee> es = jtt.list("select * from employee where age < ? order by id desc", new Object[]{30}, Employee.class);
assertThat(new Integer(es.size()),is(2));
assertThat(es.get(1).getName(),is("jack"));
}
@Test
public void testCount() throws IOException, SQLException {
JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class);
int total = jtt.count("select count(1) from employee", null);
assertThat(total,is(4));
}
public class Student {
private Integer id;
private String name;
private String nothing;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Transient
public String getNothing() {
return nothing;
}
public void setNothing(String nothing) {
this.nothing = nothing;
}
}
这个字段会被跳过
@Test
public void testSave() throws Exception {
JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class);
Student s = new Student();
s.setName("michael");
s.setNothing("nothing");
jtt.save(s);
}
@Id
public Integer getId() {
return id;
}
例子
@Test
public void testGet() throws NoIdAnnotationFoundException, NoColumnAnnotationFoundException, IOException, SQLException {
JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class);
Employee e = jtt.get(Employee.class, 3);
assertThat(e.getName(),is("jim"));
}
@Test
public void testUpdate() throws Exception {
JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class);
Employee e = jtt.get(Employee.class, 1);
e.setAge(23);
jtt.update(e);
}
@Test
public void testBatchUpdate() throws SQLException, IOException {
build();
JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class);
List<Object[]> params = new ArrayList<Object[]>();
Object[] p1 = new Object[]{23,"jack"};
params.add(p1);
Object[] p2 = new Object[]{29,"tim"};
params.add(p2);
jtt.batchUpdate("update employee set age = ? where name = ?", params);
}
@Test
public void testDelete() throws Exception {
JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class);
Employee e = new Employee();
e.setId(1);
jtt.delete(e);
}
getJdbcTemplate
你依然可以使用原始的 JdbcTemplate. 调用 JdbcTemplateTool.getJdbcTemplate() to
getJdbcTemplate 就可以了。
CREATE USER 'travis'@'%' IDENTIFIED BY ''; GRANT ALL ON jtt_test.* TO 'travis'@'%'; flush privileges;
创建一张表
DROP TABLE IF EXISTS `employee`; CREATE TABLE `employee` ( `id` int(11) NOT NULL, `name` varchar(300) NOT NULL, `join_date` datetime NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `employee` */ insert into `employee`(`id`,`name`,`join_date`,`age`) values (1,'jack','2014-09-22 00:00:00',23),(2,'ted','2014-08-30 00:00:00',25),(3,'jim','2014-06-22 00:00:00',33);
<?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-3.0.xsd"> <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="jdbcUrl"><value>jdbc:mysql://localhost:3306/jtt_test?characterEncoding=utf8</value></property> <property name="driverClass"><value>com.mysql.jdbc.Driver</value></property> <property name="user"><value>travis</value></property> <property name="password"><value></value></property> </bean> <bean id = "jdbcTemplate" class = "org.springframework.jdbc.core.JdbcTemplate"> <property name = "dataSource" ref="dataSource"/> </bean> <bean id="jdbcTemplateTool" class="org.crazycake.jdbcTemplateTool.JdbcTemplateTool"> <property name = "jdbcTemplate" ref="jdbcTemplate" /> </bean> </beans>
import java.sql.Timestamp; import javax.persistence.Id; public class Employee { private Integer id; private String name; private Timestamp joinDate; private Integer age; @Id public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Timestamp getJoinDate() { return joinDate; } public void setJoinDate(Timestamp joinDate) { this.joinDate = joinDate; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } }
import static org.hamcrest.CoreMatchers.is; import static org.junit.Assert.assertThat; import java.util.List; import org.crazycake.jdbcTemplateTool.JdbcTemplateTool; import org.junit.Test; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests; @ContextConfiguration(locations={"classpath:spring.xml"}) public class HelloJTTTest extends AbstractJUnit4SpringContextTests{ @Test public void testSave(){ JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class); Employee e = new Employee(); e.setId(4); e.setName("billy"); Date now = new Date(); e.setJoinDate(new Timestamp(now.getTime())); e.setAge(33); try { jtt.save(e); } catch (Exception e1) { e1.printStackTrace(); } } }
@Test public void testList(){ JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class); List<Employee> es = jtt.list("select * from employee where age < ? order by id desc", new Object[]{30}, Employee.class); assertThat(new Integer(es.size()),is(2)); assertThat(es.get(1).getName(),is("jack")); }
@Test public void testCount() throws IOException, SQLException { JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class); int total = jtt.count("select count(1) from employee", null); assertThat(total,is(4)); }
public class Student { private Integer id; private String name; private String nothing; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Transient public String getNothing() { return nothing; } public void setNothing(String nothing) { this.nothing = nothing; } }
这个字段会被跳过
@Test public void testSave() throws Exception { JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class); Student s = new Student(); s.setName("michael"); s.setNothing("nothing"); jtt.save(s); }
@Id public Integer getId() { return id; }
例子
@Test public void testGet() throws NoIdAnnotationFoundException, NoColumnAnnotationFoundException, IOException, SQLException { JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class); Employee e = jtt.get(Employee.class, 3); assertThat(e.getName(),is("jim")); }
@Test public void testUpdate() throws Exception { JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class); Employee e = jtt.get(Employee.class, 1); e.setAge(23); jtt.update(e); }
@Test public void testBatchUpdate() throws SQLException, IOException { build(); JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class); List<Object[]> params = new ArrayList<Object[]>(); Object[] p1 = new Object[]{23,"jack"}; params.add(p1); Object[] p2 = new Object[]{29,"tim"}; params.add(p2); jtt.batchUpdate("update employee set age = ? where name = ?", params); }
@Test public void testDelete() throws Exception { JdbcTemplateTool jtt = super.applicationContext.getBean("jdbcTemplateTool",JdbcTemplateTool.class); Employee e = new Employee(); e.setId(1); jtt.delete(e); }
getJdbcTemplate
你依然可以使用原始的
JdbcTemplate. 调用JdbcTemplateTool.getJdbcTemplate()to getJdbcTemplate就可以了。
