JDBC Template基本使用方法详解

<dependencies>
  <dependency>
   <groupid>junit</groupid>
   <artifactid>junit</artifactid>
   <version>4.11</version>

  </dependency>
  <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
  <dependency>
   <groupid>org.springframework</groupid>
   <artifactid>spring-context</artifactid>
   <version>5.2.5.release</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
  <dependency>
   <groupid>org.springframework</groupid>
   <artifactid>spring-core</artifactid>
   <version>5.2.5.release</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.springframework/spring-beans -->
  <dependency>
   <groupid>org.springframework</groupid>
   <artifactid>spring-beans</artifactid>
   <version>5.2.5.release</version>
  </dependency>

  <!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
  <dependency>
   <groupid>org.springframework</groupid>
   <artifactid>spring-aop</artifactid>
   <version>5.2.5.release</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
  <dependency>
   <groupid>org.springframework</groupid>
   <artifactid>spring-jdbc</artifactid>
   <version>5.2.5.release</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
  <dependency>
   <groupid>org.springframework</groupid>
   <artifactid>spring-tx</artifactid>
   <version>5.2.5.release</version>
  </dependency>

  <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
  <dependency>
   <groupid>mysql</groupid>
   <artifactid>mysql-connector-java</artifactid>
   <version>8.0.19</version>
  </dependency>
 </dependencies>

<?xml version="1.0" encoding="utf-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"
    xsi:schemalocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">


  <!--配置datasource-->
  <bean id="datasource" class="org.springframework.jdbc.datasource.drivermanagerdatasource">
    <property name="driverclassname" value="com.mysql.jdbc.driver"></property>
    <property name="url" value="jdbc:mysql://localhost:3306/demo1?servertimezone=utc"></property>
    <property name="username" value="root"></property>
    <property name="password" value="root"></property>
  </bean>
  
<!--使用jdbctemplate工具类-->
  <bean id="jdbctemplate" class="org.springframework.jdbc.core.jdbctemplate">
    <property name="datasource" ref="datasource"></property>
  </bean>
</beans>

import org.junit.test;
import org.springframework.context.applicationcontext;
import org.springframework.context.support.classpathxmlapplicationcontext;
import org.springframework.jdbc.core.jdbctemplate;
public class test {
@test
  public void test(){
    applicationcontext applicationcontext=new classpathxmlapplicationcontext("myapplication.xml");
    jdbctemplate springtemplate = (jdbctemplate) applicationcontext.getbean("jdbctemplate");
    springtemplate.execute("create table test(id int,username varchar(10))");
  }
}

public void testupdate(){
    string sql = "insert into student(name,sex) values(?,?)";
    jdbctemplate.update(sql,new object[]{"张飞","男"});
  }

  public void testupdate2(){
    string sql = "update student set sex=? where id=?";
    jdbctemplate.update(sql,"女",1003);
  }

  public void testbatchupdate(){
    string[] sqls={
        "insert into student(name,sex) values('关羽','女')",
        "insert into student(name,sex) values('刘备','男')",
        "update student set sex='女' where id=2001"
    };
    jdbctemplate.batchupdate(sqls);
  }

  public void testbatchupdate2(){
    string sql = "insert into selection(student,course) values(?,?)";
    list<object[]> list = new arraylist<object[]>();
    list.add(new object[]{1005,1001});
    list.add(new object[]{1005,1003});
    jdbctemplate.batchupdate(sql,list);
  }

public void testquerysimple1(){
    string sql = "select count(*) from student";
    int count = jdbctemplate.queryforobject(sql,integer.class);
    system.out.println(count);
  }

  public void testquerysimple2(){
    string sql = "select name from student where sex=?";
    list<string> names = jdbctemplate.queryforlist(sql,string.class,"女");
    system.out.println(names);
  }

  public void testquerymap1(){
    string sql = "select * from student where id = ?";
    map<string,object> stu = jdbctemplate.queryformap(sql,1003);
    system.out.println(stu);
  }

  public void testquerymap2(){
    string sql = "select * from student";
    list<map<string,object>> stus = jdbctemplate.queryforlist(sql);
    system.out.println(stus);
  }

  public void testqueryentity1(){
    string sql = "select * from student where id = ?";
    student stu = jdbctemplate.queryforobject(sql, new studentrowmapper(), 1004);
    system.out.println(stu);
  }
  @org.junit.test
  public void testqueryentity2(){
    string sql = "select * from student";
    list<student> stus = jdbctemplate.query(sql,new studentrowmapper());
    system.out.println(stus);
  }

  private class studentrowmapper implements rowmapper<student>{
    public student maprow(resultset resultset, int i) throws sqlexception {
      student stu = new student();
      stu.setid(resultset.getint("id"));
      stu.setname(resultset.getstring("name"));
      stu.setsex(resultset.getstring("sex"));
      stu.setborn(resultset.getdate("born"));
      return stu;
    }
  }