封装jdbc 实现简单的JdbcTemplate
测试结果:
/** * 测试 * @param args */public static void main(String[] args) {/** * 使用ThreadLocal实现~确保每一个线程都访问自己的connection * 实际上用main函数测试并不好，使用Thread测试更好～ */JdbcTemplateImp jt = new JdbcTemplateImp();//开启事物jt.openTransaction();//查询List<Map> list = jt.query("select * from emp");for(Map map : list){System.out.println(map.get("ENAME"));System.out.println(map.get("SAL"));}//添加方法1jt.execute("insert into myemp values(8888,'xiaomaha','CLERK',7782,'20-5月-1984',9999,666,20)");//添加方法2jt.execute("insert into myemp values(?,?,?,?,?,?,?,?)", new Object[]{9999,"xiaomaha2","CLERK",7788,"20-5月-1984",9988,66,20});//删除jt.execute("delete from myemp where ename=?",new Object[]{"xiaomaha"});//删除 故意写错，看回滚是否成功! 注释后程序正常~~~~~~~~jt.execute("delete from myemp where err_ename=?",new Object[]{"xiaomaha"});//关闭事物jt.closeTransaction();}接口:
package org.jdbc;import java.util.List;import java.util.Map;public interface JdbcTemplate {/** * 查询接口 * @param sql * @return */public List<Map> query(String sql);/** * 查询接口 * @param sql * @param parameters 参数 * @return */public List<Map> query(String sql ,Object[] parameters);public void openTransaction();public void closeTransaction();/** * 执行接口 * @param sql */public void execute(String sql);/** * 执行接口 * @param sql * @param parameters 参数 */public void execute(String sql ,Object[] parameters);}实现:
package org.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/** * jdbc实现连接池实际更好，但这个例子主要是想实现jdbcTemplate功能 * 所以map来装connection，都没写,使用连接池不需要每次关闭connection,只需要回收给map对象 * 简单的pool其实很简单，但考虑到安全及性能需要花费大量时间，和精力 * 这个一个简单的jdbcTemplate实现如下 * @author Administrator * */public class JdbcTemplateImp implements JdbcTemplate{public JdbcTemplateImp(){init();};static{try {//一个application只需要加载一次,写成xml读取更好,通过xml或properties文件读取就可以通用了!～略Class.forName("oracle.jdbc.driver.OracleDriver");} catch (ClassNotFoundException e) {e.printStackTrace();}}private ThreadLocal<Connection> conn = new ThreadLocal<Connection>(); //ThreadLocalprivate void init(){try{//写成xml读取更好,通过xml或properties文件读取就可以通用了!～略String url="jdbc:oracle:thin:@localhost:1521:ora",uname= "scott",passw="tiger";Connection con = DriverManager.getConnection(url,uname,passw);conn.set(con);}catch(Exception e){e.printStackTrace();}}public List<Map> query(String sql , Object[] parameters){PreparedStatement ps = null;try {ps = conn.get().prepareStatement(sql);for(int i=0 ;i<parameters.length ;i++){ps.setObject(i+1, parameters[i]);}ResultSet rs = ps.executeQuery();return resultToMap(rs);} catch (SQLException e) {e.printStackTrace();return null;}finally{try {ps.close();} catch (SQLException e) {e.printStackTrace();}}}public List<Map> query(String sql){PreparedStatement ps = null;try {ps = conn.get().prepareStatement(sql);ResultSet rs = ps.executeQuery();return resultToMap(rs);} catch (SQLException e) {e.printStackTrace();return null;}finally{try {ps.close();} catch (SQLException e) {e.printStackTrace();}}}/** * 转换为map类型 * @param rs * @return */private List<Map> resultToMap(ResultSet rs){List<Map> list = new ArrayList<Map>();try {ResultSetMetaData rsmd = rs.getMetaData();int colCount = rsmd.getColumnCount();while(rs.next()){Map<String,Object> map = new HashMap<String,Object>();for(int i=1 ;i<=colCount ;i++){String colName = rsmd.getColumnName(i);Object value = rs.getObject(colName);map.put(colName, value);}list.add(map);}} catch (SQLException e) {e.printStackTrace();}finally{try {rs.close();} catch (SQLException e) {e.printStackTrace();}}return list;}public void execute(String sql) {PreparedStatement ps = null;try {ps = conn.get().prepareStatement(sql);ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();try {conn.get().rollback();} catch (SQLException e1) {e1.printStackTrace();}}finally{try {ps.close();} catch (SQLException e) {e.printStackTrace();}}}public void execute(String sql, Object[] parameters) {PreparedStatement ps = null;try {ps = conn.get().prepareStatement(sql);for(int i=0 ;i<parameters.length ;i++){ps.setObject(i+1,parameters[i]);}ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();try {conn.get().rollback();} catch (SQLException e1) {e1.printStackTrace();}}finally{try {ps.close();} catch (SQLException e) {e.printStackTrace();}}}public void openTransaction() {Connection con = null;try {con = conn.get();con.setAutoCommit(false);} catch (SQLException e) {e.printStackTrace();}}public void closeTransaction() {Connection con = null;try {con = conn.get();con.commit();} catch (SQLException e) {try {con.rollback();} catch (SQLException e1) {e1.printStackTrace();}e.printStackTrace();} finally{try {con.close();} catch (SQLException e) {e.printStackTrace();}}}public Connection getConn() {return conn.get();}/** * 测试 * @param args */public static void main(String[] args) {/** * 使用ThreadLocal实现~确保每一个线程都访问自己的connection * 实际上用main函数测试并不好，使用Thread测试更好～ */JdbcTemplateImp jt = new JdbcTemplateImp();//开启事物jt.openTransaction();//查询List<Map> list = jt.query("select * from emp");for(Map map : list){System.out.println(map.get("ENAME"));System.out.println(map.get("SAL"));}//添加方法1jt.execute("insert into myemp values(8888,'xiaomaha','CLERK',7782,'20-5月-1984',9999,666,20)");//添加方法2jt.execute("insert into myemp values(?,?,?,?,?,?,?,?)", new Object[]{9999,"xiaomaha2","CLERK",7788,"20-5月-1984",9988,66,20});//删除jt.execute("delete from myemp where ename=?",new Object[]{"xiaomaha"});//删除 故意写错，看回滚是否成功! 注释后程序正常~~~~~~~~jt.execute("delete from myemp where err_ename=?",new Object[]{"xiaomaha"});//关闭事物jt.closeTransaction();}}使用时需要添加对应数据库jar包
dbank下载:http://dl.dbank.com/c0g1bg6ayl