配置数据源
<context:property-placeholder location="jdbc.properties"/> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean>配置JdbcTemplate
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean>基本的增删改操作
public class Main { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); JdbcTemplate jdbcTemplate = context.getBean("jdbcTemplate", JdbcTemplate.class); String sql = "insert into account1(name, money) values (?,?)"; jdbcTemplate.update(sql, "楚渊", 200); } }批量的增删改操作
public class Main { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); JdbcTemplate jdbcTemplate = context.getBean("jdbcTemplate", JdbcTemplate.class); String sql = "insert into account1(name, money) values (?,?)"; List<Object[]> accounts = new ArrayList<>(); accounts.add(new Object[]{"晏明修", 123}); accounts.add(new Object[]{"李玉", 234}); accounts.add(new Object[]{"宋居寒", 345}); jdbcTemplate.batchUpdate(sql, accounts); } }查询一条数据
public class Main { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); JdbcTemplate jdbcTemplate = context.getBean("jdbcTemplate", JdbcTemplate.class); String sql = "select id,`name`,money from account1 where id = ?"; //指定结果集的封装方式，列名与属性名的对应通过sql语句中的别名来实现，不支持级联属性的查询 RowMapper<Account> rowMapper = new BeanPropertyRowMapper<>(Account.class); Account account = jdbcTemplate.queryForObject(sql, rowMapper, 13); System.out.println(account); } }查询多条数据
public class Main { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); JdbcTemplate jdbcTemplate = context.getBean("jdbcTemplate", JdbcTemplate.class); String sql = "select id,`name`,money from account1 where id > ?"; RowMapper<Account> rowMapper = new BeanPropertyRowMapper<>(Account.class); List<Account> accounts = jdbcTemplate.query(sql, rowMapper, 9); for (Account account : accounts) { System.out.println(account); } } }获取单一属性值
public class Main { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); JdbcTemplate jdbcTemplate = context.getBean("jdbcTemplate", JdbcTemplate.class); String sql = "select count(*) from account1"; Integer total = jdbcTemplate.queryForObject(sql, Integer.class); System.out.println(total); } }使用具名参数的namedParameterJdbcTemplate
<bean class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate" id="namedParameterJdbcTemplate"> <constructor-arg name="dataSource" ref="dataSource"/>//有参构造器 </bean> public class Main { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); NamedParameterJdbcTemplate namedParameterJdbcTemplate = (NamedParameterJdbcTemplate) context.getBean("namedParameterJdbcTemplate"); String sql = "insert into account1(name, money) values (:name,:money)"; Map<String, Object> paramMap = new HashMap<>(); paramMap.put("name", "段白月"); paramMap.put("money", 9090); namedParameterJdbcTemplate.update(sql, paramMap); } }使用这种方式，当参数比较多的时候，更好的确认参数的对应关系，比较好维护，但是书写起来比较麻烦，此时可以使用SqlParameterSource作为参数，注意需要将参数的名称与实体类的属性名对应上
public class Main { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); NamedParameterJdbcTemplate namedParameterJdbcTemplate = (NamedParameterJdbcTemplate) context.getBean("namedParameterJdbcTemplate"); String sql = "insert into account1(name, money) values (:name,:money)"; Account account = new Account(null, "姜成禄", (float) (2534 * 1.0)); SqlParameterSource paramSource = new BeanPropertySqlParameterSource(account); namedParameterJdbcTemplate.update(sql, paramSource); } }