JdbcTemplate系列(二)----实战CRUD

╰半夏微凉° 2022-06-15 02:38 212阅读 0赞

**第一步:创建Maven**

eclipse新建Mavne选择Maven Project项目

![20170521172427724][]

选择Maven-archetype-webapp

![20170521172438917][]

输入Group id和Artifact id

![20170521172445755][]

输入好之后,点击Finsh,项目创建如下

![20170521172658843][]

查看index.jsp文件

![20170521172759616][]

发现报servlet错,这是由于缺少servlet.api所导致的,在pom.xml加入引用就可以了。

**第二步:编辑POM文件**

然后项目右键"Build path"选择ALLow output...

![20170521173600091][]

会发现项目多出来了几个目录,如下:

![20170521173701245][]

在pom.xml文件中加入以下内容:

<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/maven-v4_0_0.xsd">
      <modelVersion>4.0.0</modelVersion>
      <groupId>com.fendo.com</groupId>
      <artifactId>JDBC</artifactId>
      <packaging>war</packaging>
      <version>0.0.1-SNAPSHOT</version>
      <name>JDBC Maven Webapp</name>
      <url>http://maven.apache.org</url>
      
         <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
       
            <spring-version>4.1.2.RELEASE</spring-version>
            <commons.version>1.4</commons.version>
            <c3p0.version>0.9.1.2</c3p0.version>
            <log4j.version>1.2.17</log4j.version>
            <servlet.api.version>2.5</servlet.api.version>
            <jstl.version>1.2</jstl.version>
            <jsp.api.version>2.1</jsp.api.version>
        </properties>
      
      <dependencies>
    
    	<dependency>
    	    <groupId>junit</groupId>
    	    <artifactId>junit</artifactId>
    	    <version>4.12</version>
    	</dependency>
    	
        <dependency>
             <groupId>commons-dbcp</groupId>
             <artifactId>commons-dbcp</artifactId>
             <version>${commons.version}</version>
         </dependency>
    
         
         
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>${spring-version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-webmvc</artifactId>
                <version>${spring-version}</version>
            </dependency>
    
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-tx</artifactId>
                <version>${spring-version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>${spring-version}</version>
            </dependency>
            <dependency>
                <groupId>c3p0</groupId>
                <artifactId>c3p0</artifactId>
                <version>${c3p0.version}</version>
            </dependency>
    
            <dependency>
                <groupId>javax.transaction</groupId>
                <artifactId>transaction-api</artifactId>
                <version>1.1</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.34</version>
            </dependency>
    
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
                <version>1.7.4</version>
            </dependency>
         
    
            
            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>servlet-api</artifactId>
                <version>${servlet.api.version}</version>
                <type>jar</type>
            </dependency>
            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>jstl</artifactId>
                <version>${jstl.version}</version>
                <type>jar</type>
            </dependency>
            <dependency>
                <groupId>javax.servlet.jsp</groupId>
                <artifactId>jsp-api</artifactId>
                <version>${jsp.api.version}</version>
                <type>jar</type>
    		</dependency>
    
    
           <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>${log4j.version}</version>
            </dependency>
    
    
    
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>${spring-version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-webmvc</artifactId>
                <version>${spring-version}</version>
            </dependency>
    
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-tx</artifactId>
                <version>${spring-version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>${spring-version}</version>
            </dependency>
            <dependency>
                <groupId>c3p0</groupId>
                <artifactId>c3p0</artifactId>
                <version>${c3p0.version}</version>
            </dependency>
    
            <dependency>
                <groupId>javax.transaction</groupId>
                <artifactId>transaction-api</artifactId>
                <version>1.1</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.34</version>
            </dependency>
    
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
                <version>1.7.4</version>
            </dependency>
    
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.11</version>
            </dependency>
      </dependencies>
      <build>
        <finalName>JDBC</finalName>
      </build>
    </project>

**第三步:编辑web.xml、jdbc.properties、appliactionContext.xml文件**

修改web.xml如下:

<?xml version="1.0" encoding="UTF-8"?>
    
    <web-app>
      <display-name>Archetype Created Web Application</display-name>
      
      <listener>
        <listener-class>org.springframework.web.util.IntrospectorCleanupListener</listener-class>
      </listener>
      
      <servlet>
        <servlet-name>spring</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <init-param>
          <param-name>contextConfigLocation</param-name>
          <param-value>classpath:*appliactionContext.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
      </servlet>
      
      <servlet-mapping>
        <servlet-name>spring</servlet-name>
        <url-pattern>/</url-pattern>
      </servlet-mapping>
      
      <filter>
        <filter-name>setCharacter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
          <param-name>encoding</param-name>
          <param-value>utf-8</param-value>
        </init-param>
      </filter>
      
      <filter-mapping>
        <filter-name>setCharacter</filter-name>
        <url-pattern>/*</url-pattern>
      </filter-mapping>
      
    </web-app>

在src\\main\\resources下面新建一个属性配置文件: jdbc.properties

jdbc.user=root
    jdbc.password=root
    jdbc.driverClass=com.mysql.jdbc.Driver
    jdbc.jdbcUrl=jdbc:mysql://127.0.0.1:3306/jdbc
    jdbc.initPoolSize=5
    jdbc.maxPoolSize=10

新建Spring配置文件applicationContext.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:context="http://www.springframework.org/schema/context"
    	xmlns:tx="http://www.springframework.org/schema/tx"
    	xsi:schemaLocation="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.1.xsd
    		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd">
    
        
        <context:component-scan base-package="com.fendo.dao"></context:component-scan>
    
    
    	<context:property-placeholder location="classpath:jdbc.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>
            
            <property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>
            <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
        </bean>
        
    
        
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            <property name="dataSource" ref="dataSource"></property>
        </bean>
        
        
        <bean id="userDao" class="com.fendo.impl.UserDaoImpl">
            <property name="jdbcTemplate" ref="jdbcTemplate"></property>
        </bean>
        
          
    	<bean id="namedParameterJdbcTemplate"  
    	    class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">  
    	    <constructor-arg ref="dataSource"></constructor-arg>  
    	</bean>
    
    </beans>

新建好之后如下:

![20170521175108579][]

**第四步:新建实体类与测试方法**

![20170522211850935][]

对应的SQL文件为:

CREATE TABLE `student` (
      `sid` int(11) NOT NULL AUTO_INCREMENT,
      `sname` varchar(255) DEFAULT NULL,
      `sage` varchar(255) DEFAULT NULL,
      `ssex` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`sid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8;

新建UserDao类:

package com.fendo.dao;
    
    
    import java.util.List;
    
    import com.fendo.entity.User;
    
    public interface UserDao {
    	
        public void addUser(User user);
    
        public void deleteUser(int id);
    
        public void updateUser(User user);
    
        public String searchUserName(int id);
        
        public User searchUser(int id);
        
        public List<User> findAll();
    
    }

建立类：UserDaoAnnotation，基于注解的查询方法

package com.fendo.dao;
    
    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.stereotype.Component;
    
    import com.fendo.entity.User;
    
    @Component//基本注解
    public class UserDaoAnnotation {
    
    	    @Autowired
    	    public JdbcTemplate jdbcTemplate;
    	    
    	    public User get(Integer sid){
    	        String sql="select sid,sname,ssex from student where sid=?";
    	        RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class);
    	        User user=jdbcTemplate.queryForObject(sql, rowMapper, sid);
    	        return user;
    	    }
    }

实现RowMapper接口

package com.fendo.dao;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import org.springframework.jdbc.core.RowMapper;
    
    import com.fendo.entity.User;
    
    public class UserRowMapper implements RowMapper<User>{
    
    	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        // rs 已经指向每一条数据，不需要自己调用 next，将rs指向数据 转换 User对象
    	User user = new User();
    	user.setSid(rs.getString("sid"));
    	user.setSname(rs.getString("sname"));
    	return user;
    	}
    
    }

创建User实体类:

package com.fendo.entity;
    
    public class User {
    
    	private String sid;
    	private String sname;
    	private String sage;
    	private String ssex;
    	public String getSid() {
    		return sid;
    	}
    	public void setSid(String sid) {
    		this.sid = sid;
    	}
    	public String getSname() {
    		return sname;
    	}
    	public void setSname(String sname) {
    		this.sname = sname;
    	}
    	public String getSage() {
    		return sage;
    	}
    	public void setSage(String sage) {
    		this.sage = sage;
    	}
    	public String getSsex() {
    		return ssex;
    	}
    	public void setSsex(String ssex) {
    		this.ssex = ssex;
    	}
    }

创建实现类:

package com.fendo.impl;
    
    import java.util.List;
    
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.support.JdbcDaoSupport;
    
    import com.fendo.dao.UserDao;
    import com.fendo.dao.UserRowMapper;
    import com.fendo.entity.User;
    
    
    public class UserDaoImpl extends JdbcDaoSupport implements UserDao{
    
    	public void addUser(User user) {
            String sql = "insert into student values(?,?,?,?)";
            this.getJdbcTemplate().update(sql, user.getSid(), user.getSname(),
                    user.getSage(),user.getSsex());
        }
    
        public void deleteUser(int id) {
            String sql = "delete from student where sid=?";
            this.getJdbcTemplate().update(sql, id);
    
        }
    
        public void updateUser(User user) {
            String sql = "update student set sname=?,sage=? where sid=?";
            this.getJdbcTemplate().update(sql, user.getSname(),
                    user.getSage(), user.getSid());
        }
    
        public String searchUserName(int sid) {// 简单查询，按照ID查询，返回字符串
            String sql = "select sname from student where sid=?";
            // 返回类型为String(String.class)
            return this.getJdbcTemplate().queryForObject(sql, String.class, sid);
    
        }
    
        public List<User> findAll() {// 复杂查询返回List集合
            String sql = "select * from student";
            return this.getJdbcTemplate().query(sql, new UserRowMapper());
    
        }
    
        public User searchUser(int id) {
            String sql="select * from student where sid=?";
            return this.getJdbcTemplate().queryForObject(sql, new UserRowMapper(), id);
        }
    
    }

测试类1:

package com.fendo.test;
    
    
    import java.util.List;
    
    import org.junit.Test;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    
    import com.fendo.dao.UserDao;
    import com.fendo.entity.User;
    
    public class Temp {
    	
    	@Test//增
        public void demo1(){
            User user=new User();
            user.setSid("3");
            user.setSname("admin");
            user.setSage("18");
            
            ApplicationContext applicationContext=new ClassPathXmlApplicationContext("appliactionContext.xml");
            UserDao dao=(UserDao) applicationContext.getBean("userDao");
            dao.addUser(user);
            
        }
        
        @Test//改
        public void demo2(){
            User user=new User();
            user.setSid("3");
            user.setSname("fendo");
            user.setSage("18");
            
            ApplicationContext applicationContext=new ClassPathXmlApplicationContext("appliactionContext.xml");
            UserDao dao=(UserDao) applicationContext.getBean("userDao");
            dao.updateUser(user);
        }
        
        @Test//删
        public void demo3(){
            ApplicationContext applicationContext=new ClassPathXmlApplicationContext("appliactionContext.xml");
            UserDao dao=(UserDao) applicationContext.getBean("userDao");
            dao.deleteUser(3);
        }
        
        @Test//查（简单查询，返回字符串）
        public void demo4(){
            ApplicationContext applicationContext=new ClassPathXmlApplicationContext("appliactionContext.xml");
            UserDao dao=(UserDao) applicationContext.getBean("userDao");
            String name=dao.searchUserName(3);
            System.out.println(name);
        }
        
        @Test//查（简单查询，返回对象）
        public void demo5(){
            ApplicationContext applicationContext=new ClassPathXmlApplicationContext("appliactionContext.xml");
            UserDao dao=(UserDao) applicationContext.getBean("userDao");
            User user=dao.searchUser(3);
            System.out.println(user.getSname());
        }
        
        @Test//查（复杂查询，返回对象集合）
        public void demo6(){
            ApplicationContext applicationContext=new ClassPathXmlApplicationContext("appliactionContext.xml");
            UserDao dao=(UserDao) applicationContext.getBean("userDao");
            List<User> users=dao.findAll();
            System.out.println(users.size());
        }
    }

测试类2:

package com.fendo.test;
    
    
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.sql.DataSource;
    
    import org.junit.Test;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
    import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
    import org.springframework.jdbc.core.namedparam.SqlParameterSource;
    
    import com.fendo.dao.UserDao;
    import com.fendo.dao.UserDaoAnnotation;
    import com.fendo.entity.User;
    import com.fendo.impl.UserDaoImpl;
    
    public class Temp1 {
    
        private ApplicationContext ctx=null;
        private JdbcTemplate jdbcTemplate;
        private UserDaoAnnotation userDaoAnnotation;
        
        private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
        {
            ctx=new ClassPathXmlApplicationContext("appliactionContext.xml");
            jdbcTemplate=(JdbcTemplate)ctx.getBean("jdbcTemplate");
            userDaoAnnotation=ctx.getBean(UserDaoAnnotation.class);
            namedParameterJdbcTemplate=ctx.getBean(NamedParameterJdbcTemplate.class);
        }
        
        
        /**
     　　* 使用具名参数时, 可以使用 update(String sql, SqlParameterSource paramSource) 方法进行更新操作
     　　* 1. SQL 语句中的参数名和类的属性一致!即上边employee中属性一致
     　　* 2. 使用 SqlParameterSource 的 BeanPropertySqlParameterSource 实现类作为参数. 
     　　*/
        @Test
        public void testNamedParameterJdbcTemplate2(){
        	String sql="insert into student(sname,sage,ssex) values(:sname,:sage,:ssex)";
        	User user=new User();
    		user.setSname("fendo");
    		user.setSage("18");
    		user.setSsex("男");
    		SqlParameterSource paramSource=new BeanPropertySqlParameterSource(user);
    		namedParameterJdbcTemplate.update(sql,paramSource);
        }
        
        
    	//可以为参数起名字
    	//好处: 若有多个参数, 则不用再去对应位置, 直接对应参数名, 便于维护
    	//缺点: 较为麻烦.
            @Test
    	public void testNamedParameterJdbcTemplate(){
    			String sql="insert into student(sname,sage,ssex) values(:sname,:sage,:ssex)";
    			Map<String, Object> paramMap=new HashMap();
    			paramMap.put("sname", "fendo");
    			paramMap.put("sage", "18");
    			paramMap.put("ssex", "男");
    			namedParameterJdbcTemplate.update(sql, paramMap);
    	}
    
    
    
        
         //对基于注解的方法测试
         @Test
         public void tesUserDao(){
             System.out.println(userDaoAnnotation.get(2));
         }
         
         
         //获取单个列的值, 或做统计查询
         //使用 queryForObject(String sql, Class<Long> requiredType)
        @Test
         public void testQueryForObject2(){
             String sql="select count(sid) from student";
             long count=jdbcTemplate.queryForObject(sql, Long.class);
             System.out.println(count);
         }
         
         //查到实体类的集合
         //注意调用的不是 queryForList 方法
         @Test
         public void testQueryForList(){
             String sql="select sid,sname,sage from student where sid>?";
             RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class);
             List<User> user=jdbcTemplate.query(sql, rowMapper, 1);
             
             System.out.println(user);
         }
         
         /**
          * 从数据库中获取一条记录, 实际得到对应的一个对象
          * 注意不是调用 queryForObject(String sql, Class<Employee> requiredType, Object... args) 方法!
          * 而需要调用 queryForObject(String sql, RowMapper<Employee> rowMapper, Object... args)
          * 1. 其中的 RowMapper 指定如何去映射结果集的行, 常用的实现类为 BeanPropertyRowMapper
          * 2. 使用 SQL 中列的别名完成列名和类的属性名的映射. 例如 last_name lastName
          * 3. 不支持级联属性. JdbcTemplate 到底是一个 JDBC 的小工具, 而不是 ORM 框架
          */
         @Test
         public void testQueryForObject(){
        	 
        	 // String sql="select id,last_name lastName,email,dept_id as \"department.id\" from tables where id=?";
             String sql="select sid,sname,sage,ssex as \"department.sex\" from student where sid=?";
             
             RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class);
             User employee=jdbcTemplate.queryForObject(sql, rowMapper,1);
             
             System.out.println(employee);
         }
         
         //执行批量更新: 批量的 INSERT, UPDATE, DELETE
         //最后一个参数是 Object[] 的 List 类型: 因为修改一条记录需要一个 Object 的数组, 
         //那么多条不就需要多个 Object 的数组
         @Test
         public void testBatchUpdate(){
             String sql="insert into student(sid,sname,sage) values(?,?,?)";
             
             List<Object[]> batchArgs=new ArrayList<Object[]>();
             
             batchArgs.add(new Object[]{"11","fendo","15"});
             batchArgs.add(new Object[]{"21","ckse","16"});
             batchArgs.add(new Object[]{"31","gogo","17"});
             batchArgs.add(new Object[]{"41","asde","18"});
             
             jdbcTemplate.batchUpdate(sql,batchArgs);
         }
         
         //执行 INSERT, UPDATE, DELETE，只修改sql语句，即可
         @Test
         public void testUpdate(){
             String sql="update student set sname=? where sid=?";
             jdbcTemplate.update(sql, "权", "1");
         }
         
         //测试数据库连接池是否连接成功
         @Test
         public void testDataSource() throws SQLException {
             DataSource  dataSource=ctx.getBean(DataSource.class);
             System.out.println(dataSource.getConnection());
         }
    
    }

完整示例: [ http://download.csdn.net/detail/u011781521/9849263][http_download.csdn.net_detail_u011781521_9849263]

[20170521172427724]: /images/20220615/0ed7c6936270472a8ea346661aa2c372.png
[20170521172438917]: /images/20220615/36d9b07dd1f749159c3e1f03523054b8.png
[20170521172445755]: /images/20220615/e5157d7d124d4c3d92b3e83c3f14486e.png
[20170521172658843]: /images/20220615/78fc4f05b05040558f54d168c7bac99b.png
[20170521172759616]: /images/20220615/be6f78c57a9d4cd58064f8109c9e9d3d.png
[20170521173600091]: /images/20220615/632afb9ed2a84e0399a28f35547fb534.png
[20170521173701245]: /images/20220615/9d7eafa2f883471fa04abbe29b28b65a.png
[20170521175108579]: /images/20220615/28a1fec368384335a6cda8594f3d3b3b.png
[20170522211850935]: /images/20220615/c3ff0fe44e4849efa2ed5b4c6017a51e.png
[http_download.csdn.net_detail_u011781521_9849263]: http://download.csdn.net/detail/u011781521/9849263