一、JdbcTemplatey实例化对象

TestSQL类private DataSource dataSource;public DataSource getDataSource() {return dataSource;}public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;}public void queryTable(){//这种的JdbcTemplate是实例化对象的，不是注入的JdbcTemplate template = new JdbcTemplate(this.dataSource);String sql  = "select * from user";List<Map<String,Object>> dataList = template.queryForList(sql);for(Map<String,Object> rowData :dataList){System.out.println(rowData.get("userId")+"\t"+rowData.get("userName"));}}public static void main(String[] args) {TestSQL testSQL = (TestSQL)SpringUtil.getBean("TestSQL");testSQL.queryTable();}spring配置文件
需要注入dataSource
<bean id="TestSQL" class="com.test.TestSQL"><property name="dataSource" ref="dataSource"></property>
</bean>

二、JdbcTemplatey用Spring管理

TestSQL类//JdbcTemplatey用spring注入private JdbcTemplate template ;public JdbcTemplate getTemplate() {return template;}public void setTemplate(JdbcTemplate template) {this.template = template;}public void queryTable() {String sql = "select * from user";List<Map<String, Object>> dataList = this.template.queryForList(sql);for (Map<String, Object> rowData : dataList) {System.out.println(rowData.get("userId") + "\t" +rowData.get("userName"));}}public static void main(String[] args) {TestSQL2 testSQL = (TestSQL2) SpringUtil.getBean("TestSQL2");testSQL.queryTable();}
spring配置文件   <!--注入template--><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><!--当没有注入dataSource的时候，将无法使用jdbcTemplate，因为还没有给数据源赋值--><property name="dataSource" ref="dataSource"></property></bean><bean id="TestSQL2" class="com.test.TestSQL2"><property name="template" ref="jdbcTemplate"></property></bean>

三、执行DDL语句

3.1创建temp表

 TestSQL类//JdbcTemplatey用spring注入private JdbcTemplate template ;public JdbcTemplate getTemplate() {return template;}public void setTemplate(JdbcTemplate template) {this.template = template;}public void create() {String sql = "create table temp (tempId int,tempName varchar(20))";this.template.execute(sql);}public static void main(String[] args) {TestSQL_DDL testSQL = (TestSQL_DDL) SpringUtil.getBean("TestSQL_DDL");testSQL.create();//执行成功后会创建temp表 字段为int tempId和varchar类型的tempName}spring配置文件<!--注入template--><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property></bean><bean id="TestSQL_DDL" class="com.test.TestSQL_DDL"><property name="template" ref="jdbcTemplate"></property></bean>

3.2修改temp表

	 public void alter() {String sql = "ALTER table temp add password varchar(10) ";this.template.execute(sql);}public static void main(String[] args) {TestSQL_DDL testSQL = (TestSQL_DDL) SpringUtil.getBean("TestSQL_DDL");testSQL.alter();//执行成功后会修改temp表，增加字段为varchar password}

3.3删除temp表

  public void drop() {String sql = "drop table temp ";this.template.execute(sql);}public static void main(String[] args) {TestSQL_DDL testSQL = (TestSQL_DDL) SpringUtil.getBean("TestSQL_DDL");testSQL.drop();//执行成功后会删除temp表 }

四、执行DML语句

4.1insert语句—statement对象

TestSQL类private JdbcTemplate template ;public JdbcTemplate getTemplate() {return template;}public void setTemplate(JdbcTemplate template) {this.template = template;}//相当于jdbc的statement对象的操作public void insert() {String sql = "insert into admin (userName,password) value ('aaa','bbb')";this.template.update(sql);}public static void main(String[] args) {TestSQL_DML testSQL = (TestSQL_DML) SpringUtil.getBean("TestSQL_DML");testSQL.insert();//执行后会在admin表插入userName为aaa和password为bbb}
spring配置文件<bean id="TestSQL_DML" class="com.test.TestSQL_DML"><property name="template" ref="jdbcTemplate"></property></bean>

4.2insert语句—Preparedstatemen对象（建议）

     /*相当于jdbc的Preparedstatement对象的操作,创建一个匿名内部类PreparedStatementSetter对象(建议)*/public void insert_2() {String sql = "insert into admin (userName,password) value (?,?)";this.template.update(sql, new PreparedStatementSetter() {//匿名内部类的写法@Overridepublic void setValues(PreparedStatement ps) throws SQLException {ps.setString(1,"abc");ps.setString(2,"bbb");}});}

4.3insert语句—Bean对象（不建议）

	/*相当于jdbc的Preparedstatement对象的操作,创建并返回PreparedStatementSetter对象(不建议)*/public void insert_3() {String sql = "insert into admin (userName,password) value (?,?)";this.template.update(new PreparedStatementCreator() {@Overridepublic PreparedStatement createPreparedStatement(Connection connection) throws SQLException {PreparedStatement statement = connection.prepareStatement(sql);statement.setString(1,"test1");statement.setString(2,"testpwd");return statement;}});}

4.4使用Object数组对象（建议）

  /*相当于jdbc的Preparedstatement对象的操作,使用object数组对象(建议)*/public void insert_4() {String sql = "insert into admin (userName,password) value (?,?)";Object[] paramArray = {"aabc","dcee"};this.template.update(sql,paramArray);}

4.5使用Map作为参数执行insert语句

 public void insertParameter() {String sql = "insert into admin (userName,password) value (:userName,:password)";NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.template.getDataSource());Map<String,Object> map = new HashMap<>();//map中的key要和参数一致,map中的数据可以比value中指定的参数多，但不能少map.put("userName","test2");map.put("password","testpwd2");template.update(sql,map);}

4.6使用Bean作为参数执行insert语句

public void insertBean(){String sql = "insert into admin (userName,password) value (:userName,:password)";NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.template.getDataSource());/*Bean的属性要和value中参数的属性一致，可以多不可以少*/User user = new User();user.setUserName("abbvc");user.setPassword("avvv");user.setUserId(123);BeanPropertySqlParameterSource propertySqlParameterSource = new BeanPropertySqlParameterSource(user);template.update(sql,propertySqlParameterSource);}

五、执行Query查询语句

5.1 queryForList（返回Map）

默认返回的是map，返回的是多行数据

public void queryForList() {String sql = "select * from admin";List<Map<String, Object>> dataList = template.queryForList(sql);for (Map<String, Object> map : dataList) {System.out.println(map.get("userId") + "\t" + map.get("userName"));}}
测试输出
1	admin
2	user
3	adb

5.2 queryForList的注意点

直接使用queryForList不能成功返回bean类型 只能返回一个字段

public void queryForListBean() {/*错误的写法String sql = "select * from admin";List<User>dataList = template.queryForList(sql,User.class);for(User user : dataList){System.out.println(user.getUserId()+"\t"+user.getUserName()+"\t"+user.getPassword());}*/String sql = "select userName from admin";List<String> dataList = template.queryForList(sql, String.class);for (String str : dataList) {System.out.println(str);}}
错误的写法执行会报返回多个字段的错误，错误为Exception in thread "main" org.springframework.jdbc.IncorrectResultSetColumnCountException: Incorrect column count: expected 1, actual 5
正确返回数据为：adminuseradb

5.3 queryForMap（返回单行数据）

public void queryForMap() {String sql = "select * from admin where userId = 2";Map<String, Object> row = template.queryForMap(sql);if (row != null) {System.out.println(row.get("userId") + "\t" + row.get("userName"));}}
测试输出：
2	user

5.4 queryForObject（返回聚合函数）

public void queryForObject() {String sql = "select count(1) from admin";Long userCount = template.queryForObject(sql, long.class);System.out.println("userCount=" + userCount);}测试输出：userCount=9

5.5 query返回所有数据

返回所有数据使用query函数，参数是sql语句+BeanPropertyRowMapper

public void query() {String sql = "select * from admin";/*	注意这个和BeanPropertySqlParameterSource对象的区别BeanPropertyRowMapper将数据映射成一个对象BeanPropertySqlParameterSource将Bean对象中的值赋值给命名参数*/BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);List<User> dataList = template.query(sql, rowMapper);for (User user : dataList) {System.out.println(user.getUserId() + "\t" + user.getUserName());}}测试输出：1	admin2	user3	adb

5.6 query_Pre（预编译）

查询语句中包过预编译对象，？形式

public void query_Pre() {String sql = "select * from admin where userName like ? and password like ? ";String[] Strarray = {"%a%", "%1%"};BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);List<User> dataList = template.query(sql, rowMapper, Strarray);for (User user : dataList) {System.out.println(user.getUserId() + "\t" + user.getUserName());}}测试输出：1	admin3	adb

5.7 queryNamePre（查询带命名参数）

使用：形式

public void queryNamePre() {String sql = "select * from admin where userName like :userName and password like :password ";NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template.getDataSource());User user = new User();user.setUserName("%a%");user.setPassword("%1%");BeanPropertySqlParameterSource propertySqlParameterSource =new BeanPropertySqlParameterSource(user);BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);List<User> dataList = namedTemplate.query(sql, propertySqlParameterSource, rowMapper);for (User row : dataList) {System.out.println(row.getUserId() + "\t" + row.getUserName());}}测试输出：1	admin3	adb