Spring框架针对dao层的jdbcTemplate操作crud之query查询数据操作 —— 查询表，返回结果为某一个对象。
查询数据库表，如果用户表sw_user中存在一条username为lisi的数据，则将这条数据的对象返回
使用原始的方法，操作如下：
1、先写一个工具类，有实现MySQL数据库连接的方法，和关闭数据库连接、关闭ResultSet 结果集、关闭PreparedStatement 的方法。代码如下：
package com.swift; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DBUtil { //连接MySQL数据库工具 public static Connection getConn() { Connection conn=null; try { Class.forName("com.mysql.jdbc.Driver"); try { conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/sw_database?user=root&password=root"); } catch (SQLException e) { e.printStackTrace(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } return conn; } //关闭数据库连接、sql连接、结果集 public static void closeAll(Connection conn,PreparedStatement ps,ResultSet rs) { if(conn!=null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } if(ps!=null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if(rs!=null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
使用这个DBUtil来查询数据库表中是否有一条名字为lisi的数据，将这条数据按User对象返回
package com.swift; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class QueryOneObject { public static User queryOneObject() { Connection conn=DBUtil.getConn(); PreparedStatement ps=null; ResultSet rs=null; User user=null; try { ps=conn.prepareStatement("select * from sw_user where username=? and password=?"); ps.setString(1, "lisi"); ps.setString(2, "abcdef"); } catch (SQLException e) { e.printStackTrace(); } try { rs=ps.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } try { while(rs.next()) { String username=rs.getString("username"); String password=rs.getString("password"); user=new User(username,password); } } catch (SQLException e) { e.printStackTrace(); } return user; } }
测试类SeverletDemo
package com.swift; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; @WebServlet("/demo") public class ServletDemo extends HttpServlet { private static final long serialVersionUID = 1L; public ServletDemo() { super(); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.getWriter().append("Served at: ").append(request.getContextPath()); User user=QueryOneObject.queryOneObject(); response.getWriter().append(user.toString()); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }
结果：
下面将用JdbcTemplate的方法完成上面过程：
再用同样语句查询 数据库表中的zhangsan 这条记录用一个对象返回
浏览器中得到结果如上图，成功获得zhansan对象
在JdbcTemplateDemo类中增加查询返回一个对象的方法queryOneObject()
数据源DriverManagerDataSource不变
jdbcTemplate的方法有所改变，使用queryForObject(sql, new MyRowMapper(), "zhangsan","123456");
其中第二个参数是一个接口RowMapper<User>的实现类，可以使用匿名内部类，我使用的是单独的类MyRowMapper的对象
它要实现重载的方法mapRow(ResultSet rs, int num)只做两件事
一用ResultSet得到所需User对象数据
二用User进行封装得到对象并返回
package com.swift; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.datasource.DriverManagerDataSource; import org.springframework.stereotype.Component; @Component(value="jdbcTemplateDemo") public class JdbcTemplateDemo { public User queryOneObject() { DriverManagerDataSource dataSource=new DriverManagerDataSource(); dataSource.setDriverClassName("com.mysql.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/sw_database"); dataSource.setUsername("root"); dataSource.setPassword("root"); JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource); String sql="select * from sw_user where username=? and password=?"; User user=jdbcTemplate.queryForObject(sql, new MyRowMapper(), "zhangsan","123456"); return user; } public boolean delete(String username) { DriverManagerDataSource dataSource=new DriverManagerDataSource(); dataSource.setDriverClassName("com.mysql.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/sw_database"); dataSource.setUsername("root"); dataSource.setPassword("root"); JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource); int count=jdbcTemplate.update("delete from sw_user where username=?", username); if(count!=0) { return true; } return false; } } //没有用匿名内部类，注意MyRowMapper后没有<User> class MyRowMapper implements RowMapper<User> { @Override public User mapRow(ResultSet rs, int num) throws SQLException { String username=rs.getString("username"); String password=rs.getString("password"); User user=new User(username,password); return user; } }
实现结果的Servlet类
package com.swift; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; @WebServlet("/demo") public class ServletDemo extends HttpServlet { private static final long serialVersionUID = 1L; public ServletDemo() { super(); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.getWriter().append("Served at: ").append(request.getContextPath()); User user=QueryOneObject.queryOneObject(); response.getWriter().append(user.toString()); //使用JdbcTemplat的queryForObject方法 ApplicationContext context=new ClassPathXmlApplicationContext("aop.xml"); JdbcTemplateDemo jdbcTemplateDemo=(JdbcTemplateDemo) context.getBean("jdbcTemplateDemo"); User user1=jdbcTemplateDemo.queryOneObject(); response.getWriter().println(); response.getWriter().append(user1.toString()); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }
扫描注解的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:aop="http://www.springframework.org/schema/aop" 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.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd"> <!-- 开启注解扫描——对象和属性 --> <context:component-scan base-package="com.swift"></context:component-scan> <!-- 开启aop注解方法 --> <aop:aspectj-autoproxy></aop:aspectj-autoproxy> </beans>
日志文件log4j.properties，运行代码能够看清Spring配置文件执行细节
代码如下：
#日志框架
#最早是System.out.print
#org.apache.log4j.Logger
#org.apache.commons.logging.Log
#java.util.logging.Logger
#org.slf4j.Logger 面向接口编程，选择实现（选择都使用这个）
#日志等级
#TRACE：详细等级，堆栈信息
#debug：类似于System.out.print
#info：类似于Hibernate的show_sql
#warn：不影响运行， 只是提示
#error：出现异常
#全局日志等级配置,输出位置
log4j.rootLogger=info,stdout,logfile
#stdout控制器
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
#输出格式
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c]:%L - %m%n
#文件路径输出
log4j.appender.logfile=org.apache.log4j.RollingFileAppender
#默认输出到tomcat的根路径或者运行测试工程的根路径
log4j.appender.logfile.File=pss.log
log4j.appender.logfile.MaxFileSize=512KB
# Keep three backup files.
log4j.appender.logfile.MaxBackupIndex=3
# Pattern to output: date priority [category] - message
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d %p [%c] - %m%n
#局部日志等级:就近原则
#log4j.logger.固定，后面添加想那个包使用不一样的日志等级
#log4j.logger.cn.itsource.pss=debug
Never waste time any more, Never old man be a yong man
