JDBCtemplate使用
插入
String sql="insert into user (name,deptid) values (?,?)"; int count= jdbcTemplate.update(sql, new Object[]{"caoyc",3});
批量插入
String sql="insert into user (name,deptid) values (?,?)"; List<Object[]> batchArgs=new ArrayList<Object[]>(); batchArgs.add(new Object[]{"caoyc",6}); batchArgs.add(new Object[]{"zhh",8}); batchArgs.add(new Object[]{"cjx",8}); jdbcTemplate.batchUpdate(sql, batchArgs);
删除
String sql="update user set name=?,deptid=? where id=?"; jdbcTemplate.update(sql,new Object[]{"zhh",5,51}); //或者 String sql="delete from user where id=?"; jdbcTemplate.update(sql,51);
读
String sql="select id,name,deptid from user where id=?"; RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class); User user= jdbcTemplate.queryForObject(sql, rowMapper,52);
读取多个对象
String sql="select id,name,deptid from user"; RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class); List<User> users= jdbcTemplate.query(sql, rowMapper); for (User user : users) { System.out.println(user); }
获取某个记录某列或者count、avg、sum等函数返回唯一值
String sql="select count(*) from user"; int count= jdbcTemplate.queryForObject(sql, Integer.class);
Spring+Spring MVC+JDBCtemplate
依赖
spring-jdbc
db.properties
jdbc.user=root jdbc.password=123456 jdbc.driverClass=com.mysql.jdbc.Driver jdbc.jdbcUrl=jdbc:mysql:///test
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" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd 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-4.3.xsd"> <context:component-scan base-package="com.proc"></context:component-scan> <context:property-placeholder location="classpath:db.properties"/> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="user" value="${jdbc.user}"></property> <property name="password" value="${jdbc.password}"></property> <property name="driverClass" value="${jdbc.driverClass}"></property> <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
使用
@Repository public class UserDao { @Autowired private JdbcTemplate jdbcTemplate; public User get(int id){ String sql="select id,name,deptid from user where id=?"; RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class); return jdbcTemplate.queryForObject(sql, rowMapper,id); } }
SpringBoot+JDBCtemplate
依赖
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- 引入jdbc支持 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <!-- 连接MySQL数据库 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.46</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- 生成api文档 --> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger2</artifactId> <version>2.2.2</version> </dependency> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger-ui</artifactId> <version>2.2.2</version> </dependency> </dependencies>
application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/test_db?serverTimezone=GMT%2B8&useSSL=false spring.datasource.username=root spring.datasource.password=123456 spring.datasource.driver-class-name=com.mysql.jdbc.Driver
使用
@Repository public class UserDao { @Autowired private JdbcTemplate jdbcTemplate; public User get(int id){ String sql="select id,name,deptid from user where id=?"; RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class); return jdbcTemplate.queryForObject(sql, rowMapper,id); } }