jdbcTemplate

下面是java操作数据库的模板，只需要写出简单的sql语句，再调用该模板中对应的方法，就能完成平时我们的数据库查询和更新操作了。

public class JdbcTemplate {
private Map<String,Connection>map=Collections.synchronizedMap(new

Hashtable<String,Connection>());
private static CopyOfJdbcTemplate jt;
private JdbcPool jpool=JdbcPool.getInstance();
private CopyOfJdbcTemplate(){
}
public static CopyOfJdbcTemplate getInstance(){
if(jt==null){
jt=new CopyOfJdbcTemplate();
}
return jt;
}
/*
所有查询“基类”
*/
public Object queryFormOriented(String sql,Object[]

args,PreparedStatementSetter pss,RowMapper rowmapper,Class clazz)throws Exception{
Object obj=null;
Connection conn=null;
try{
conn=jpool.getConnection();
PreparedStatement ps=conn.prepareStatement(sql);
pss.setPatameter(ps);//赋值
ResultSet rs=ps.executeQuery();
obj=rowmapper.mappObject(rs);//获取结果集
}catch(Exception e){
System.out.println(e.getMessage());
}finally{
conn.close();
}
return obj;
}
/*
联合查询 以二维数组形式返回查询结果
*
* */
public Object[][]joinQuery(String sql,final Object[]args) throws Exception{
return (Object[][])queryFormOriented(sql,args,new

PreparedStatementSetter(){
public void setPatameter(PreparedStatement ps) throws

SQLException {
if(args!=null){
for(int i=0;i<args.length;i++){
ps.setObject(i+1, args[i]);
}
}
}
},new RowMapper(){
public Object mappObject(ResultSet rs) throws Exception {
int cols=rs.getMetaData().getColumnCount();
int rows=0;
List list=new ArrayList();
while(rs.next()){
List lt=new ArrayList();
for(int i=0;i<cols;i++){
lt.add(rs.getObject(i+1));
}
list.add(lt);
rows++;
}
Object [][]objs=new Object[rows][cols];
for(int i=0;i<objs.length;i++){
List col=(List)list.get(i);
for(int j=0;j<objs[i].length;j++){
objs[i][j]=col.get(j);
}
}
return objs;
}
},null);
}
/*
查询结果为一对象
*
*/
public Object queryFormObject(String sql,final Object[]args,final Class clazz)

throws Exception{
return queryFormOriented(sql,args,new PreparedStatementSetter(){
public void setPatameter(PreparedStatement ps) throws

SQLException {
if(args!=null)
for(int i=0;i<args.length;i++){//获取参数个数
ps.setObject(i+1, args[i]);
}
}
},new RowMapper(){
Object obj=null;
Method []methods=clazz.getMethods();
String fieldName=null;
String name=null;
public Object mappObject(ResultSet rs) throws Exception{
ResultSetMetaData rsm= rs.getMetaData();
int cols=rsm.getColumnCount();
System.out.println(cols);
while(rs.next()){
obj=clazz.newInstance();
for(int i=1;i<=cols;i++){


fieldName=rsm.getColumnName(i);//得到第几列然后将获得这列的值
name=FormUtil.getSetMethodName

(fieldName.toLowerCase());
for(int

j=0;j<methods.length;j++){
if(name.equals

(methods[j].getName())){
methods

[j].invoke(obj, rs.getObject(fieldName));
break;
}
}
}
}
return obj;
}

},clazz);
}
/*
查询结果为一个对象集合
*/
public List queryFromList(String sql,final Object[]args,final Class clazz) throws

Exception{
return (List) queryFormOriented(sql,args,new

PreparedStatementSetter(){
public void setPatameter(PreparedStatement ps) throws

SQLException {
// TODO Auto-generated method stub
if(args!=null)
for(int i=0;i<args.length;i++){//获取参数

个数
ps.setObject(i+1, args[i]);
}
}},new RowMapper(){
List list=null;
Method []methods=clazz.getMethods();
String fieldName=null;
String name=null;
public Object mappObject(ResultSet rs) throws

Exception {
// TODO Auto-generated method stub
ResultSetMetaData rsm= rs.getMetaData

();
int cols=rsm.getColumnCount();
while(rs.next()){
if(list==null){
list=new ArrayList();
}
Object obj=clazz.newInstance();
for(int i=1;i<=cols;i++){


fieldName=rsm.getColumnName(i);//得到第i列 然后将获得这列的值


name=FormUtil.getSetMethodName(fieldName.toLowerCase());
for(int

j=0;j<methods.length;j++){
if

(name.equals(methods[j].getName())){


methods[j].invoke(obj, rs.getObject(fieldName));


break;
}
}
}
list.add(obj);
}
return list;
}},clazz);
}
/*
查询结果为某个值
*
*/
public Object queryFormSingleResulte(String sql,final Object[]args,final Class

clazz) throws Exception{
return queryFormOriented(sql,args,new PreparedStatementSetter(){

public void setPatameter(PreparedStatement ps) throws

SQLException {
if(args!=null)
for(int i=0;i<args.length;i++){//获取参数

个数
ps.setObject(i+1, args[i]);
}
}},
new RowMapper(){
public Object mappObject(ResultSet rs)

throws Exception {
Object obj=null;
while(rs.next()){
obj=rs.getObject(1);
}
return obj;
}},clazz);
}
/*
* 查询结果为 一个值集合
*/
public List queryFormObjectList(String sql,final Object[]args,final Class clazz)

throws Exception{
return (List)queryFormOriented(sql,args,new PreparedStatementSetter

(){

public void setPatameter(PreparedStatement ps) throws

SQLException {
if(args!=null)
for(int i=0;i<args.length;i++){//获取参数

个数
ps.setObject(i+1, args[i]);
}
}},
new RowMapper(){
public Object mappObject(ResultSet rs)

throws Exception {
Object obj=null;
List list=null;
while(rs.next()){
if(list==null){
list=new

ArrayList();
}
obj=rs.getObject(1);
list.add(obj);
}
return list;
}},clazz);
}


/*************************更新操作*********************/

/*
由于更新操作涉及事务处理，所以必须保证一个更新整个过程中必须只有一

个连接对象
这里运用到Threadlocal模式
*/
public Connection getConnection() throws Exception{
Connection conn=map.get(Thread.currentThread().getName());
if(conn==null){
conn=jpool.getConnection();
map.put(Thread.currentThread().getName(), conn);
}
return conn;
}
public Transation beginTransation() throws Exception{
Connection conn=getConnection();
conn.setAutoCommit(false);//关闭连接自动提交功能
return new Transation(conn);
}
public void close() throws Exception{
Connection conn=getConnection();
if(conn!=null){
conn.close();
map.remove(conn);
}
}
/*
所有更新“基类”
*/
public int updateDB(String sql,final Object[]args,PreparedStatementSetter pss)

throws Exception{
int rows=0;
Connection conn=getConnection();
PreparedStatement ps=conn.prepareStatement(sql);
pss.setPatameter(ps);
rows=ps.executeUpdate();
return rows;

}
public int updateDB(String sql,final Object[]args) throws Exception{
return updateDB(sql,args,new PreparedStatementSetter(){
public void setPatameter(PreparedStatement ps) throws

SQLException {
if(args!=null){
for(int i=0;i<args.length;i++){
ps.setObject(i+1, args[i]);
}
}
}

});
}

public static void main(String[] arg) {
String sql="select count(*) from orders where time like "+ "'%-01-%'";
Object []args=new Object[]{"01","02"};
try {
CopyOfJdbcTemplate jt=CopyOfJdbcTemplate.getInstance();
//for(jt.queryFormObjectList(sql,null,null)){
Object [][]objs=jt.joinQuery(sql,null);
for(int i=0;i<objs.length;i++){
for(int j=0;j<objs[i].length;j++){
System.out.print(objs[i][j]+" ");
}
System.out.println();
}
//}
} catch (Exception e) {
e.printStackTrace();
}
}
}