使用Spring Jdbc (1)-CSDN博客

本文链接：https://blog.csdn.net/voyage_mh1987/article/details/5781057

文件及其环境要求：

<?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:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd">   <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="location" value="jdbc.properties"></property> </bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.driverClassName}" /> <property name="url" value="${jdbc.url}" /> <property name="username" 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> <bean id="dao" abstract="true"> <property name="jdbcTemplate" ref="jdbcTemplate" /> </bean> <bean id="formDao" parent="dao" class="com.macower.spring.jdbc.FormJdbcDao"/> </beans>

package com.macower.spring.jdbc; public class Form { private int userId; private String userName; public Form() { super(); } public int getUserId() { return userId; } public void setUserId(int userId) { this.userId = userId; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } }

jdbc。properties

jdbc.driverClassName=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://127.0.0.1:3306/sampledb jdbc.username=root jdbc.password=root

package com.macower.spring.jdbc; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Types; import org.springframework.jdbc.core.PreparedStatementSetter; import org.springframework.jdbc.core.support.JdbcDaoSupport; public class FormJdbcDao extends JdbcDaoSupport { public void createTableByJdbcDaoSupport() { String sql = "create table tb_user(user_id int primary key, user_name varchar(60))";// 创建一张表 getJdbcTemplate().execute(sql); } public void addForm(final Form form) {// 为了form可以在内部类中使用所以必须加入final的修饰符 String sql = "insert into tb_user values(?,?)"; // PreparedStatementSetter是一个回调接口，JdbcTemplate使用sql语句创建出PreparedStatement实例后 // 将用该回调接口执行绑定的参数 getJdbcTemplate().update(sql, new PreparedStatementSetter() {// 通过匿名的内部类回调实例 @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setInt(1, form.getUserId()); ps.setString(2, form.getUserName()); } }); } /** * 更新数据形式1： getJdbcTemplate().update(sql, params); * @param form */ public void updateForm1(Form form){ String sql = "insert into tb_user values(?,?)"; Object [] params = new Object[]{form.getUserId(),form.getUserName()}; getJdbcTemplate().update(sql, params); } /** * 更新数据形式2： getJdbcTemplate().update(sql, params,Types[]); * @param form */ public void updateForm2(Form form){ String sql = "insert into tb_user values(?,?)"; Object [] params = new Object[]{form.getUserId(),form.getUserName()}; getJdbcTemplate().update(sql, params,new int[]{Types.INTEGER,Types.VARCHAR}); } public void addForm2(final Form form){ final String sql = "insert into tb_user values(?,?)"; //PreparedStatementCreator是一个回调的接口，负责创建PreparedStatement实例 getJdbcTemplate().update(new PreparedStatementCreator(){ @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, form.getUserId()); ps.setString(2,form.getUserName()); return ps; } }); } }

package com.macower.spring.jdbc; import javax.sql.DataSource; import junit.framework.TestCase; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; public class CreateATable extends TestCase { /** * 测试用JdbcTemplate模板类建立数据表 * @throws Exception */ public void testCreateATable() throws Exception { ApplicationContext ctx = new ClassPathXmlApplicationContext("app*.xml"); DataSource dataSource = (DataSource) ctx.getBean("dataSource");//从bean中读取属性 JdbcTemplate jdbcTemplate = new JdbcTemplate();//创建Jdbc模板类 jdbcTemplate.setDataSource(dataSource);//为模板类添加数据源 String sql = "create table tb_user(user_id int primary key, user_name varchar(60))";//创建一张表 jdbcTemplate.execute(sql); /*执行后在数据库中显示的结果为 * mysql> desc tb_user; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | user_id | int(11) | NO | PRI | NULL | | | user_name | varchar(60) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 2 rows in set (0.07 sec) */ } /** * 测试利用JdbcDaoSupport中的 getJdbcTemplate()进行数据的操作 * @throws Exception */ public void testCreateTableByJdbcDaoSupport() throws Exception { ApplicationContext ctx = new ClassPathXmlApplicationContext("app*.xml"); FormJdbcDao dao = (FormJdbcDao) ctx.getBean("formDao"); dao.createTableByJdbcDaoSupport(); } public void testAddForm() { Form form = new Form(); form.setUserId(1); form.setUserName("Macower"); ApplicationContext ctx = new ClassPathXmlApplicationContext("app*.xml"); FormJdbcDao dao = (FormJdbcDao) ctx.getBean("formDao"); dao.addForm(form); System.out.println("增加Form 成功"); /* * 执行该函数时得到的结果： * mysql> select * from tb_user; * +---------+-----------+ * | user_id | user_name | * +---------+-----------+ * | 1 | Macower | * +---------+-----------+ * 1 row in set (0.00 sec) */ } public void testUdpateForm1() { Form form = new Form(); form.setUserId(2); form.setUserName("Mahan"); ApplicationContext ctx = new ClassPathXmlApplicationContext("app*.xml"); FormJdbcDao dao = (FormJdbcDao) ctx.getBean("formDao"); //更新数据形式1： getJdbcTemplate().update(sql, params); dao.updateForm1(form); /* * mysql> select * from tb_user; +---------+-----------+ | user_id | user_name | +---------+-----------+ | 1 | Macower | | 2 | Mahan | +---------+-----------+ 2 rows in set (0.00 sec) */ } /** * */ public void testUdpateForm2() { Form form = new Form(); form.setUserId(3); form.setUserName("voyage"); ApplicationContext ctx = new ClassPathXmlApplicationContext("app*.xml"); FormJdbcDao dao = (FormJdbcDao) ctx.getBean("formDao"); //更新数据形式2： getJdbcTemplate().update(sql, params,Types[]); dao.updateForm2(form); /* * mysql> select * from tb_user; +---------+-----------+ | user_id | user_name | +---------+-----------+ | 1 | Macower | | 2 | Mahan | | 3 | voyage | +---------+-----------+ 3 rows in set (0.00 sec) */ } public void testAddForm2(){ Form form = new Form(); form.setUserId(4); form.setUserName("Macower"); ApplicationContext ctx = new ClassPathXmlApplicationContext("app*.xml"); FormJdbcDao dao = (FormJdbcDao) ctx.getBean("formDao"); //更新数据形式2： getJdbcTemplate().update(getJdbcTemplate().update(new PreparedStatementCreator(){ //..}); dao.addForm2(form); /*执行后查询后的结果 * mysql> select * from tb_user; +---------+-----------+ | user_id | user_name | +---------+-----------+ | 1 | Macower | | 2 | Mahan | | 3 | voyage | | 4 | Macower | +---------+-----------+ 4 rows in set (0.00 sec) */ } }