Spring | JdbcTemplate & Mysql

版权所有,如需转载,注明出处

                -----张汉东


 Spring提供的JDBC抽象框架coredatasourceobjectsupport四个不同的包组成.

org.springframework.jdbc.core包里定义了提供核心功能的类,其中包含了一个用语JdbcTemplateDAO基础类.我就是用这个基础类来实战连接Mysql数据库的.

对于没有Spring的日子的代码,这样的代码用来把记录集存储到一个对象中:

Object vo = new Object();

 vo.setXX(rs.getXType(fieldName1));

vo.setYY(rs.getYType(fieldName2));

Spring,提供了这样一个接口RowMapper,其方法是public Object mapRow(ResultSet rs,int  index),来完成这样的工作.

对于:

while(rs.next()) {

Object vo = new Object();

vo.setXX(rs.getXType(fieldName1)); vo.setYY(rs.getYType(fieldName2));

……

results.add(vo);

}

Spring中用new RowMapperResultReader(new RowMapper())来取代.

这样,JDBC中的查询过程,就被如下一句话所取代:

JdbcTemplate.query(sql,params,new RowMapperResultReader(new RowMapper()));

这个JdbcTemplatequery()方法封装了对JDBC底层API的调用,以及一些回调方法. 回调方法也是Spring框架的一种基本方法.

程序中使用了DAO来封装了对数据库的操作……JdbcTemplate的使用需要有一个DataSource的支持,所以在配置文件中,我们首先要配置一个SpringDriverManagerDataSource,然后将这个DataSource配置到JdbcTemplate里.接着将JdbcTemplate配置到DAO层.最后将DAO配置到Model层:

具体的代码如下:(连接数据库为Mysql)

.cn.zhd.DAO

UserDAO接口:

package cn.zhd.DAO;

import cn.zhd.Model.User;

import java.util.List;

public interface UserDAO {

     public void selectWithTemp();

     public List select(String where);

     public void update(String how);

     public void insert(User u);

     public User selectById(String id);

     public void insertBatchData(final List<User> user);

}

DAO接口实现类

package cn.zhd.DAO.Imp;

 

import cn.zhd.DAO.UserDAO;

import cn.zhd.Model.User;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

 

import org.springframework.jdbc.core.BatchPreparedStatementSetter;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowCallbackHandler;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.RowMapperResultReader;

 

 

public class UserDAOImp implements UserDAO{

     private JdbcTemplate jt;

 

     public JdbcTemplate getJt() {

            return jt;

     }

    

     public void setJt(JdbcTemplate jt) {

            this.jt = jt;

     }

    

     //Inner Class提供一个RresultSet中的row的映射对象

     class UserRowMapper implements RowMapper{

            public Object mapRow(ResultSet rs,int index) throws SQLException{

                   User u=new User();

                   u.setId(rs.getString("Id"));

                   u.setName(rs.getString("Name"));

                   u.setPassword(rs.getString("Password"));

                   return u;

            }

     }

     public void selectWithTemp(){

            String sql="select * from admin";

            System.out.println("Id"+"/t"+"Name"+"/t"+"Password");

            jt.query(sql,new RowCallbackHandler(){//一个实现了回调接口的类

                   //此方法为回调方法,每读取一行ResultSet被调用一次,它采用的是Statement, not a PreparedStatement

                   public void processRow(ResultSet rs) throws SQLException{

                         

                          System.out.println(rs.getString("Id")+"/t"+rs.getString("Name")+"/t"+rs.getString("Password"));

                   }

 

            });

           

     }

     //多行查询

     public List select(String where){

            List list=null;

            String sql="select * from admin"+”/t”+where;//这里不能少”/t”,教训

            list=jt.query(sql,new RowMapperResultReader(new UserRowMapper()));

            return list;

     }

    

     public User selectById(String id){

            String sql="select * from admin where id=?";

            final User u=new User();

            final Object[] params=new Object[]{id};

            jt.query(sql,params,new RowCallbackHandler(){

                   public void processRow(ResultSet rs)throws SQLException{

                          u.setId(rs.getString("ID"));

                          u.setName(rs.getString("Name"));

                          u.setPassword(rs.getString("Password"));

                          System.out.println(rs.getString("Name")+"/t"+rs.getString("Password"));

                   }

            });

            return u;

     }

     public void update(String how){

            String sql=how;

            jt.update(sql);

     }

     //将插入数据封装为一个对象作为参数传递给JdbcTemplate

     public void insert(User u){

            String sql="insert into admin(id,name,password) values(null,?,?)";

            Object[] params=new Object[]{u.getName(),u.getPassword()};

            jt.update(sql,params);

     }

//批量插入数据

     public void insertBatchData(final List<User> user){

            String sql="insert into admin(id,name,password) values(null,?,?)";

            BatchPreparedStatementSetter setter=new BatchPreparedStatementSetter(){

                   public int getBatchSize(){

                          return user.size();

                   }

                   // PreparedStatement 设置value

                   public void setValues(PreparedStatement ps,int index){

                          User u= user.get(index);

                          try{

                                 ps.setString(1,u.getName());

                                 ps.setString(2,u.getPassword());

                                

                          }catch(SQLException e){

                                 e.printStackTrace();

                          }

                   }

            };

            jt.batchUpdate(sql, setter);

     }

}

 

Model

 

cn.zhd.Model.User.java

package cn.zhd.Model;

import java.util.List;

import cn.zhd.DAO.UserDAO;

 

public class User {

      private String name;

      private String id;

      private String password;

      private UserDAO dao;

      public User(){

             

      }

      public User(String name, String password){

             this.name = name;

             this.password = password;

      }

      

      public void setDao(UserDAO dao){

             this.dao=dao;

      }

      

      public String getId(){

             return id;

      }

      

      public void setId(String id){

             this.id=id;

      }

      

      public String getName(){

             return name;

      }

      

      public void setName(String name){

             this.name=name;

      }

 

      public String getPassword() {

            return password;

      }

 

      public void setPassword(String password) {

            this.password = password;

      }

    

      public void getInfo(String id){

             List list=dao.select("where id="+id);

 

             User u=(User)list.get(0);

             

             this.id=id;

             this.name=u.getName();

             this.password=u.getPassword();

 

      }

      

      public void insert(){

             dao.insert(this);

      }

      

      public void update(String how){

             dao.update(how);

      }

      

      public void update(){

             dao.update("update admin set name='"+name+"',password='"+password+"'where id="+id);

      }

      

      public List selectWithTemp(String where) {

             return dao.select(where);

      }

      //得到查询结果

      public void selectWithTemp(){

             dao.selectWithTemp();

      }

      //id查询

      public User selectById(String id){

             return dao.selectById(id);

      }

      //批量插入数据

      public void insertBatchData(final List<User> user){

             dao.insertBatchData(user);

      }
}

 

.xml配置文件beans.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

 

<beans>

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

       <property name="driverClassName">

            <value>com.mysql.jdbc.Driver</value>

        </property>

      

       <property name="url">

            <value>jdbc:mysql:///students</value>

        </property>

       

        <property name="username">

            <value>root</value>

        </property>

       

        <property name="password">

            <value></value>

        </property>

    </bean>

   

     <bean id="jdbcTemplate"  class="org.springframework.jdbc.core.JdbcTemplate">

        <property name="dataSource">

        <ref bean="dataSource"/>

        </property>

     </bean>

     

     <bean id="userDAO" class="cn.zhd.DAO.Imp.UserDAOImp">

        <property name="jt">

        <ref bean="jdbcTemplate" />

        </property>

     </bean>

     

     <bean id="user" class="cn.zhd.Model.User">

        <property name="dao">

        <ref bean="userDAO"/>

        </property>

        <property name="name">

        <value>zjd</value>

        </property>

        <property name="password">

        <value>888</value>

        </property>

     </bean> 

</beans>

 

.测试类 JavaTest.java

package cn.zhd.Model;

 

import java.util.ArrayList;

import java.util.List;

 

import org.springframework.beans.factory.BeanFactory;

import org.springframework.beans.factory.xml.XmlBeanFactory;

import org.springframework.core.io.ClassPathResource;

import org.springframework.core.io.Resource;

 

 

 

public class UserTest {

     public static void main(String []args){

     //     User u=new   User("zjd","888");

            //批量插入数据

            List<User> u =  new  ArrayList<User>();

            u.add(new User("haha","123"));

            u.add(new User("hoho","234"));

            u.add(new User("zhansan","345"));

            u.add(new User("lisi","456"));

            u.add(new User("maqi","577"));

            u.add(new User("wo","899"));

            u.add(new User("admin","haha"));

            u.add(new User("fdsdf","675"));

            u.add(new User("werwr","89"));

            Resource resource=new ClassPathResource("beans.xml");

             BeanFactory factory = new XmlBeanFactory(resource);

             User user = (User) factory.getBean("user");

            // user.insert();

            // user.update("update admin set name='zbj' where id=2");

             user.insertBatchData(u);

            // user.selectWithTemp();

            // user.selectById("2");

            // user.getInfo("2");

     }

}

 

虽然做完了,但是感觉对Spring的理解还是不够深入,慢慢学习吧

发布了79 篇原创文章 · 获赞 3 · 访问量 16万+
展开阅读全文

Spring boot JdbcTemplate 自动注入失败

07-06

``` @Repository public class UserDataImpl implements UserData { @Autowired JdbcTemplate jdbcTemplate; } ``` ``` 2017-07-06 17:37:20.523 ERROR 2990 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.NullPointerException] with root cause java.lang.NullPointerException: null at com.marnon.service.UserService.userCheck(UserService.java:30) ~[classes/:na] at com.marnon.controller.MyController.logIn(MyController.java:45) ~[classes/:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_91] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_91] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_91] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_91] at org.springsource.loaded.ri.ReflectiveInterceptor.jlrMethodInvoke(ReflectiveInterceptor.java:1426) ~[springloaded-1.2.6.RELEASE.jar:1.2.6.RELEASE] at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-embed-websocket-8.5.15.jar:8.5.15] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:105) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) ~[tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478) [tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80) [tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799) [tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861) [tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455) [tomcat-embed-core-8.5.15.jar:8.5.15] at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-8.5.15.jar:8.5.15] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_91] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_91] at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-8.5.15.jar:8.5.15] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_91] ``` 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览