JDBC JdbTemplate&NamedParameterJdbcTemplate（Spring工具类）
使用该工具类需要从spring开发包中导入spring.jar和commons-logging.jar，这个模板是线程安全的。
JdbcTemplate:
public class JdbcTemplateTest {
static JdbcTemplate jdbc = new JdbcTemplate(JdbcUtils.getDataSource());
/**
* @param args
*/
public static void main(String[] args) {
User user = findUser("zhangsan");
// System.out.println("user:" + user);
// System.out.println("users:" + findUsers(3));
// System.out.println("user count:" + getUserCount());
// System.out.println("user name:" + getUserName(1));
System.out.println("data:" + getData(1));
}
static int addUser(final User user) {
jdbc.execute(new ConnectionCallback() {//想在插入的时候取主键，回调
public Object doInConnection(Connection con) throws SQLException, DataAccessException {
String sql = "insert into user(name,birthday, money) values (?,?,?) ";
PreparedStatement ps = con.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getName());
ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
ps.setFloat(3, user.getMoney());
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next())
user.setId(rs.getInt(1));
return null;
}
});
return 0;
}
static Map getData(int id) {
String sql = "select id as userId, name, money, birthday from user where id="
+ id;
return jdbc.queryForMap(sql);
}
static String getUserName(int id) {
String sql = "select name from user where id=" + id;
Object name = jdbc.queryForObject(sql, String.class);
return (String) name;
}
static int getUserCount() {
String sql = "select count(*) from user";
return jdbc.queryForInt(sql);//直接把结果返回成int型，也可以查询最大值平均值之类的
}
static List findUsers(int id) {
String sql = "select id, name, money, birthday from user where id<?";
Object[] args = new Object[] { id };
int[] argTypes = new int[] { Types.INTEGER };
List users = jdbc.query(sql, args, argTypes, new BeanPropertyRowMapper(
User.class));//查询结果是多条记录
return users;
}
static User findUser(String name) {
String sql = "select id, name, money, birthday from user where name=?";
Object[] args = new Object[] { name };
Object user = jdbc.queryForObject(sql, args, new BeanPropertyRowMapper(
User.class));//queryForObject查询如果查不到数据或者多于一条数据会报错
return (User) user;
}
static User findUser1(String name) {
String sql = "select id, name, money, birthday from user where name=?";
Object[] args = new Object[] { name };
Object user = jdbc.queryForObject(sql, args, new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setMoney(rs.getFloat("money"));
user.setBirthday(rs.getDate("birthday"));
return user;
}
});
return (User) user;
}
}
NamedParameterJdbcTemplate :包含JdbcTemplate的功能，特点是可以解析sql语句中的非？参数，而且可以直接用sql去匹配bean中的属性。
public class NamedJdbcTemplate {
static NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(
JdbcUtils.getDataSource());
/**
* @param args
*/
public static void main(String[] args) {
User user = new User();
user.setMoney(10);
user.setId(2);
System.out.println(findUser1(user));
}
static void addUser(User user) {
String sql = "insert into user(name,birthday, money) values (:name,:birthday,:money) ";
SqlParameterSource ps = new BeanPropertySqlParameterSource(user); //使用bean来设置sql中的参数
KeyHolder keyHolder = new GeneratedKeyHolder();
named.update(sql, ps, keyHolder); //获取主键
int id = keyHolder.getKey().intValue();
user.setId(id);
Map map = keyHolder.getKeys(); //如果主键是多个，可以用该方法获取成Map映射
}
static User findUser(User user) {
String sql = "select id, name, money, birthday from user "
+ "where money > :m and id < :id";
Map params = new HashMap();
// params.put("n", user.getName());
params.put("m", user.getMoney());
params.put("id", user.getId()); //使用Map来设置sql的参数
Object u = named.queryForObject(sql, params, new BeanPropertyRowMapper(
User.class)); //第三个参数是一个映射器，将获取的数据映射给Bean
return (User) u;
}
static User findUser1(User user) {
String sql = "select id, name, money, birthday from user "
+ "where money > :money and id < :id";
SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
Object u = named.queryForObject(sql, ps, new BeanPropertyRowMapper(
User.class));
return (User) u;
}
}
SimpleJdbcTemplate：在前两者基础上支持了可变参数和泛型
public class SimpleJdbcTemplateTest {
static SimpleJdbcTemplate simple = new SimpleJdbcTemplate(JdbcUtils
.getDataSource());
static User find(String name) {
String sql = "select id, name, money, birthday from user where name=? and money > ?";
User user = simple.queryForObject(sql,
ParameterizedBeanPropertyRowMapper.newInstance(User.class),
name, 100f);
return user;
//simple.getNamedParameterJdbcOperations().update(sql,paramSource,keyHolder);先获取NamedJdbcTemplate模板，再获取主键
//simple.getJdbcOperations()获取JdbcTemplate
}
}
上面代码引用的自定义工具类的代码：
public final class JdbcUtils {
private static String url = "jdbc:mysql://localhost:3306/jdbc";
private static String user = "root";
private static String password = "";
private static DataSource myDataSource = null;
private JdbcUtils() {
}
static {
try {
Class.forName("com.mysql.jdbc.Driver");
// myDataSource = new MyDataSource2();
Properties prop = new Properties();
// prop.setProperty("driverClassName", "com.mysql.jdbc.Driver");
// prop.setProperty("user", "user");
InputStream is = JdbcUtils.class.getClassLoader()
.getResourceAsStream("dbcpconfig.properties");
prop.load(is);
myDataSource = BasicDataSourceFactory.createDataSource(prop);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static DataSource getDataSource() {
return myDataSource;
}
public static Connection getConnection() throws SQLException {
// return DriverManager.getConnection(url, user, password);
return myDataSource.getConnection();
}
public static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
// myDataSource.free(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
