|
1、使用JdbcTemplate的execute()方法執行SQL語句
代碼
-
jdbcTemplate.execute(
"CREATE?TABLE?USER?(user_id?integer,?name?varchar(100))"
);??
2、如果是UPDATE或INSERT,可以用update()方法。
代碼 - jdbcTemplate.update("INSERT?INTO?USER?VALUES('"??
- ???????????+?user.getId()?+?"',?'"??
- ???????????+?user.getName()?+?"',?'"??
- ???????????+?user.getSex()?+?"',?'"??
- ???????????+?user.getAge()?+?"')"); ??
3、帶參數的更新
代碼 - jdbcTemplate.update("UPDATE?USER?SET?name?=???WHERE?user_id?=??",?new?Object[]?{name,?id});??
代碼 - jdbcTemplate.update("INSERT?INTO?USER?VALUES(?,??,??,??)",?new?Object[]?{user.getId(),?user.getName(),?user.getSex(),?user.getAge()}); ??
4、使用JdbcTemplate進行查詢時，使用queryForXXX()等方法
代碼 - int?count?=?jdbcTemplate.queryForInt("SELECT?COUNT(*)?FROM?USER");??
代碼 - String?name?=?(String)?jdbcTemplate.queryForObject("SELECT?name?FROM?USER?WHERE?user_id?=??",?new?Object[]?{id},?java.lang.String.class);??
代碼 - List?rows?=?jdbcTemplate.queryForList("SELECT?*?FROM?USER");??
代碼 - List?rows?=?jdbcTemplate.queryForList("SELECT?*?FROM?USER"); ??
- Iterator?it?=?rows.iterator(); ??
- while(it.hasNext())?{ ??
- ????Map?userMap?=?(Map)?it.next(); ??
- ????System.out.print(userMap.get("user_id")?+?"\t"); ??
- ????System.out.print(userMap.get("name")?+?"\t"); ??
- ????System.out.print(userMap.get("sex")?+?"\t"); ??
- ????System.out.println(userMap.get("age")?+?"\t"); ??
- } ??
JdbcTemplate將我們使用的JDBC的流程封裝起來，包括了異常的捕捉、SQL的執行、查詢結果的轉換等等。spring大量使用Template Method模式來封裝固定流程的動作，XXXTemplate等類別都是基于這種方式的實現。
除了大量使用Template Method來封裝一些底層的操作細節，spring也大量使用callback方式類回調相關類別的方法以提供JDBC相關類別的功能，使傳統的JDBC的使用者也能清楚了解spring所提供的相關封裝類別方法的使用。 JDBC的PreparedStatement
代碼 - final?String?id?=?user.getId(); ??
- final?String?name?=?user.getName(); ??
- final?String?sex?=?user.getSex()?+?""; ??
- final?int?age?=?user.getAge(); ??
- ??
- jdbcTemplate.update("INSERT?INTO?USER?VALUES(?,??,??,??)", ??
- ?????????????????????new?PreparedStatementSetter()?{ ??
- ?????????????????????????public?void?setValues(PreparedStatement?ps)?throws?SQLException?{ ??
- ?????????????????????????????ps.setString(1,?id); ??
- ?????????????????????????????ps.setString(2,?name);?????????? ??
- ?????????????????????????????ps.setString(3,?sex); ??
- ?????????????????????????????ps.setInt(4,?age); ??
- ?????????????????????????} ??
- ?????????????????????}); ??
- ??
代碼 - final?User?user?=?new?User(); ??
- jdbcTemplate.query("SELECT?*?FROM?USER?WHERE?user_id?=??", ??
- ????????????????????new?Object[]?{id}, ??
- ????????????????????new?RowCallbackHandler()?{ ??
- ????????????????????????public?void?processRow(ResultSet?rs)?throws?SQLException?{ ??
- ????????????????????????????user.setId(rs.getString("user_id")); ??
- ????????????????????????????user.setName(rs.getString("name")); ??
- ????????????????????????????user.setSex(rs.getString("sex").charAt(0)); ??
- ????????????????????????????user.setAge(rs.getInt("age")); ??
- ????????????????????????} ??
- ????????????????????}); ??
- ??
代碼 - class?UserRowMapper?implements?RowMapper?{ ??
- ????public?Object?mapRow(ResultSet?rs,?int?index)?throws?SQLException?{ ??
- ????????User?user?=?new?User(); ??
- ??
- ????????user.setId(rs.getString("user_id")); ??
- ????????user.setName(rs.getString("name")); ??
- ????????user.setSex(rs.getString("sex").charAt(0)); ??
- ????????user.setAge(rs.getInt("age")); ??
- ??
- ????????return?user; ??
- ????} ??
- } ??
- ??
- public?List?findAllByRowMapperResultReader()?{ ??
- ????String?sql?=?"SELECT?*?FROM?USER"; ??
- ????return?jdbcTemplate.query(sql,?new?RowMapperResultReader(new?UserRowMapper())); ??
- } ??
- ??
在getUser(id)里面使用UserRowMapper
代碼 - public?User?getUser(final?String?id)?throws?DataAccessException?{ ??
- ????String?sql?=?"SELECT?*?FROM?USER?WHERE?user_id=?"; ??
- ????final?Object[]?params?=?new?Object[]?{?id?}; ??
- ????List?list?=?jdbcTemplate.query(sql,?params,?new?RowMapperResultReader(new?UserRowMapper())); ??
- ??
- ????return?(User)?list.get(0); ??
- } ??
網上收集
org.springframework.jdbc.core.PreparedStatementCreator 返回預編譯SQL 不能于Object[]一起用
代碼 - public?PreparedStatement?createPreparedStatement(Connection?con)?throws?SQLException?{ ??
- ?return?con.prepareStatement(sql); ??
- } ??
1.增刪改
org.springframework.jdbc.core.JdbcTemplate 類(必須指定數據源dataSource)
代碼 - template.update("insert?into?web_person?values(?,?,?)",Object[]); ??
或
代碼 - template.update("insert?into?web_person?values(?,?,?)",new?PreparedStatementSetter(){?匿名內部類?只能訪問外部最終局部變量 ??
- ??
- ?public?void?setValues(PreparedStatement?ps)?throws?SQLException?{ ??
- ??ps.setInt(index++,3); ??
- }); ??
org.springframework.jdbc.core.PreparedStatementSetter 接口 處理預編譯SQL
代碼 - public?void?setValues(PreparedStatement?ps)?throws?SQLException?{ ??
- ?ps.setInt(index++,3); ??
- } ??
2.查詢JdbcTemplate.query(String,[Object[]/PreparedStatementSetter],RowMapper/RowCallbackHandler)
org.springframework.jdbc.core.RowMapper 記錄映射接口 處理結果集
代碼 - public?Object?mapRow(ResultSet?rs,?int?arg1)?throws?SQLException?{???int表當前行數 ??
- ??person.setId(rs.getInt("id")); ??
- } ??
- List?template.query("select?*?from?web_person?where?id=?",Object[],RowMapper); ??
org.springframework.jdbc.core.RowCallbackHandler 記錄回調管理器接口 處理結果集
代碼 - template.query("select?*?from?web_person?where?id=?",Object[],new?RowCallbackHandler(){ ??
- ?public?void?processRow(ResultSet?rs)?throws?SQLException?{ ??
- ??person.setId(rs.getInt("id")); ??
- }); ??
|