Spring 操作 jdbc 链接数据库
1. 新建资源文件 db.properities
jdbc.user=root jdbc.password=root jdbc.driverClass=com.mysql.jdbc.Driver jdbc.jdbcUrl=jdbc:mysql:///hibernate jdbc.initialPoolSize=5 jdbc.maxPoolSize=10
2. 新建配置文件 bean-jdbc.xml
<!-- 导入资源文件 -->
<context:property-placeholder location="classpath:db.properties"/>
<!-- 配置C3P0数据源 -->
<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> <property name="initialPoolSize" value="${jdbc.initialPoolSize}"></property> <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property> </bean>
<!-- 配置 Spring 的 JdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
3. 测试联通性, 更新 或 插入 或 删除 一条数据 使用 jdbcTemplate.update() 函数来实现
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package test; import java.sql.SQLException; import javax.sql.DataSource; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; public class Test { ApplicationContext ctx = null; JdbcTemplate jdbcTemplate = null; { ctx = new ClassPathXmlApplicationContext("bean-jdbc.xml"); jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate"); } @org.junit.Test public void testConnection() throws SQLException { DataSource ds = ctx.getBean(DataSource.class); System.out.println(ds.getConnection()); } @org.junit.Test public void testUpdata() { //更新一条数据 String sql = "update user set name = ? where id = ?"; int update = jdbcTemplate.update(sql, "Jack", 1); System.out.println(update); //插入一条数据 String sql2 = "insert into user(id, name, age) values(?,?,?)"; int update2 = jdbcTemplate.update(sql2, "5","hello",40); System.out.println(update2); //删除一条数据 String sql3 = "delete from user where id = ?"; int update3 = jdbcTemplate.update(sql3, 5); System.out.println(update3); } }
4. 批量更新 或 插入, 删除数据
String sql4 = "insert into user(name, age) values(?,?)"; List<Object[]> batchArgs = new ArrayList(); batchArgs.add(new Object[] {"a1",10}); batchArgs.add(new Object[] {"a2",20}); batchArgs.add(new Object[] {"a3",30}); batchArgs.add(new Object[] {"a4",40}); jdbcTemplate.batchUpdate(sql4, batchArgs);
5. 查询一条语句
@org.junit.Test public void testSelect() { //使用 sql 中的别名 可以使对象和sql字段一一对应 String sql = "select id, name userName, age from user where id = ?"; //指定如何去映射结果集的行, 常用的实现类为 BeanPropertyRowMapper RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class); User user = jdbcTemplate.queryForObject(sql, rowMapper, 1); System.out.println(user.getUserName()); }
5. 查询一组对象
@org.junit.Test public void testListSelect() { //使用 sql 中的别名 可以使对象和sql字段一一对应 String sql = "select id, name userName, age from user where id > ?"; RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class); List<User> user = jdbcTemplate.query(sql, rowMapper, 5); for(User u: user) { System.out.println(u); } }
6. 统计查询, 查询数据条数
@org.junit.Test public void testListSelectCount() { String sql = "select count(id) from user"; Long long1 = jdbcTemplate.queryForObject(sql, Long.class); System.out.println(long1); }