模板模式之JDBC

最新推荐文章于 2025-07-19 11:02:34 发布

原创最新推荐文章于 2025-07-19 11:02:34 发布 · 489 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#设计模式 #模板模式 #JDBC

Java 专栏收录该内容

1 篇文章

订阅专栏

正文

这篇博文主要记录以及分享下模板模式在jdbc中的应用

PS:部分代码借鉴老邓，纯手打嘿嘿

首先目录结构如下图所示

程序执行步骤

最简单的jdbc的应用步骤如下：
1、连接数据库之前首先要确定URL，即所需要连接数据库的地址以及数据库名称，其次提供用户名及密码
然后获取到Connection对象的实例:conn
2、随后执行sql语句，获取到结果集ResultSet rs;
3、将结果集rs中的数据一个一个的set进实体类中
但是这样用的一个最大的缺点就是不能实现代码的复用，在一个项目中，连接数据库的操作可能有几百上千
次，要写几百上千这样相似的代码很显然是很糟糕的。这样应用模板模式将上述代码实现简单的封装便显得
很有必要了，但是这只是最初级的封装，具体的优化还是要看具体的项目

源代码如下：

初级版jdbc应用：

//	private static final String URL = "jdbc:mysql://localhost:3306/MYTEST";
//	private static final String USERNAME = "root";
//	private static final String PASSWORD = "1064521309";
//	private static final String CLASSNAME = "com.mysql.jdbc.Driver";
//	private static String sql = "select * from USER where UID = 1";
//	public static void main(String[] args) throws SQLException {
//		// TODO Auto-generated method stub
//		
//		Connection conn = getConnection();
//		try {
////			System.out.println(Class.forName(CLASSNAME).getName());
//			PreparedStatement ps =  (PreparedStatement) conn.prepareStatement(sql);
//			ResultSet rs = ps.executeQuery();
//			User user = new User();
//			while(rs.next()){
//				user.setUID(Integer.parseInt(rs.getString(1)));
//				user.setUNAME(rs.getString(2));
//				user.setSEX(rs.getString(3));
//			}
//			System.out.println(user.toString());
////			System.out.println(rs.getMetaData().getColumnCount());
////			while(rs.next()){
////				System.out.println(rs.getString(3));
////			}
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		conn.close();
//		
//	}
//	public static Connection getConnection(){
//		Connection conn = null;
//		try {
//			Class.forName(CLASSNAME);
//			conn = (Connection) DriverManager.getConnection(URL, USERNAME, PASSWORD);
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return conn;
//	}

改进之后如下：

AbstractTemplate.java

package com.example.hyf;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public abstract class AbstractTemplate {
	public abstract Connection getConnection();
	public abstract Object mapRow(ResultSet rs,Class cla);
	public void close(Connection conn){
		try {
			if(!conn.isClosed()&&conn!= null){
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void setParameter(PreparedStatement ps,Object[] args){
		for(int i=0;i< args.length;i++){
			try {
				ps.setObject(i+1, args[i]);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public Object query(String sql,Object[] args,Class cla){
		Connection conn = getConnection();
		Object obj = null;
		ResultSet rs = null;
		try {
			PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
			if(ps!=null){
				setParameter(ps, args);
			}
			rs = ps.executeQuery();
			obj = mapRow(rs, cla);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(conn);
		}
		return obj;
	}
}

JdbcTemplate.java

package com.example.hyf;
import java.lang.reflect.Method;
import java.sql.DriverManager;
import java.sql.ResultSet;
import javax.management.RuntimeErrorException;
import com.mysql.jdbc.Connection;
public class JdbcTemplate extends AbstractTemplate {
	private static final String URL = "jdbc:mysql://localhost:3306/MYTEST";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "1064521309";
	private static final String CLASSNAME = "com.mysql.jdbc.Driver";
	@Override
	public Connection getConnection() {
		// TODO Auto-generated method stub
		Connection conn = null;
		try {
			Class.forName(CLASSNAME);
			conn = (Connection) DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	@Override
	public Object mapRow(ResultSet rs, Class cla) {
		// TODO Auto-generated method stub
		Object obj = null;
		Method[] methods = cla.getMethods();
		try {
			obj = cla.newInstance();//反射创建对象实例
			while(rs.next()){
				int count = rs.getMetaData().getColumnCount();
				for(int i=0;i< count;i++){
					String str = rs.getMetaData().getColumnName(i + 1);
					System.out.println(str);
					Object val = rs.getObject(str);
					System.out.println(val.getClass().getName());
					for(Method method:methods){
						if(method.getName().equalsIgnoreCase("set"+str)){
							System.out.println(method.getName());
							method.invoke(obj, val);
							break;
						}
					}
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
		return obj;
	}
}

jdbaTest.java

package com.example.hyf;
import com.example.bean.User;
public class jdbcTest {
	public static void main(String[] args) {
		String sql = "select UID,UNAME,SEX from USER where UID = ?";
		Object[] mArgs = {2};
		JdbcTemplate jdbcTemplate = new JdbcTemplate();
		User user = (User) jdbcTemplate.query(sql, mArgs, User.class);
		System.out.println(user.toString());
		
	}
}

实体类user.java

package com.example.bean;
public class User {
	private Integer UID;
	private String UNAME;
	private String SEX;
	public Integer getUID() {
		return UID;
	}
	public void setUID(Integer uID) {
		UID = uID;
	}
	public String getUNAME() {
		return UNAME;
	}
	public void setUNAME(String uNAME) {
		UNAME = uNAME;
	}
	public String getSEX() {
		return SEX;
	}
	public void setSEX(String sEX) {
		SEX = sEX;
	}
	public String toString(){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append(this.UID).append("\t")
		.append(this.UNAME).append("\t")
		.append(this.SEX);
		return sBuffer.toString();
	}
}