JdbcTemplate 简单实践一 (CURD试水-XML配置)
1.1 Maven配置 pom.xml
<?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>spring_jdbc</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.5.4</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.3</version>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
</dependency>
</dependencies>
</project>
1.2 编写dao
package cn.spring.domain;
public class Account {
private int id;
private String name;
private double balance;
public void Account() {}
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 Double getBalance() {
return balance;
}
public void setBalance(Double balance) {
this.balance = balance;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", balance=" + balance +
'}';
}
}
1.3 jdbc.properties
jdbc.drvier=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=root
1.4 applicationContext
<?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 https://www.springframework.org/schema/context/spring-context.xsd"> <!--加载jdbc.properties--> <context:property-placeholder location="jdbc.properties"/> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="${jdbc.drvier}"/> <property name="jdbcUrl" value="${jdbc.url}"/> <property name="user" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean> </beans>
1.5 编写测试类
package cn.spring.test;
import cn.spring.domain.Account;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateTest {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 1) execute方法:可以用于执行任何SQL语句,因为没有返回值一般用于执行DDL语句;
*
* 2) update方法及batchUpdate方法:
* update方法用于执行新增、修改、删除等语句;
* batchUpdate方法用于执行批处理相关语句;
*
* 3) query方法及queryForXXX方法:用于执行查询相关语句;
* JdbcTemplate查询-RowMapper返回自定义对象
* JdbcTemplate查询-BeanPropertyRowMapper返回自定义对象
*
* 4) call方法:用于执行存储过程、函数相关语句。
*
*/
@Test
public void testCreateTable() {
/**
* execute
*/
String sql = "CREATE TABLE account("
+ "id INT PRIMARY KEY AUTO_INCREMENT,"
+ "name VARCHAR(40) NOT NULL,"
+ "balance DOUBLE NOT NULL DEFAULT 0"
+ ")ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1";
System.out.println(sql);
jdbcTemplate.execute(sql);
}
@Test
public void testQueryOne() {
/**
* 1) queryForObject
* 2) JdbcTemplate查询-BeanPropertyRowMapper返回自定义对象
*/
String sql = "SELECT * FROM account WHERE name=? LIMIT 1 ";
final Account account = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Account>(Account.class), "lele");
System.out.println(account);
/**
* 3) queryForMap
*/
Map<String, Object> accountMap = jdbcTemplate.queryForMap(sql,"lele");
System.out.println(accountMap);
}
@Test
public void testQueryAll() {
String sql = "SELECT * FROM account";
/**
* 1) query
* 2) JdbcTemplate查询-BeanPropertyRowMapper返回自定义对象
*/
List<Account> accountList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Account>(Account.class));
for (Account account : accountList){
System.out.println(account);
}
/**
* 3) queryForList
*/
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
for (Map<String,Object> map : maps){
System.out.println(map);
}
/**
* 4) JdbcTemplate查询-RowMapper返回自定义对象
*/
List<Account> accountRowMapper = jdbcTemplate.query(sql, new RowMapper<Account>() {
public Account mapRow(ResultSet resultSet, int i) throws SQLException {
Account account = new Account();
account.setId(resultSet.getInt("id"));
account.setName(resultSet.getString("name"));
account.setBalance(resultSet.getDouble("balance"));
return account;
}
});
for (Account account : accountRowMapper){
System.out.println(account);
}
}
@Test
public void testQueryCount() {
String sql = "SELECT COUNT(1) FROM account";
Long aLong = jdbcTemplate.queryForObject(sql, Long.class);
System.out.println(aLong);
}
@Test
public void testInsert() {
String sql = "INSERT INTO account (name,balance) VALUES (?,?)";
int lele = jdbcTemplate.update(sql, "leon", 1197);
System.out.println(lele);
}
@Test
public void TestUpdate() {
String sql = "UPDATE account SET balance=? WHERE name=?";
int lele = jdbcTemplate.update(sql, 5000, "lele");
System.out.println(lele);
}
@Test
public void testDelete() {
String sql = "DELETE FROM account WHERE name=?";
int lele = jdbcTemplate.update(sql, "lele");
System.out.println(lele);
}
@Test
public void testBatchUpdate() {
}
}
Nice to see you all!