JDBC从入门到熟练(二)

import java.sql.*;
import java.util.List;
//Dao工厂类
public class DaoFactory {
	private static String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static String url="jdbc:sqlserver://localhost:1433;DatabaseName=News";
	private static String sql="insert userInfo values('admin','admin',getdate())";
	private static String user="sa";
	private static String pwd="sa";
	
	//1.公共方法是获得数据库链接对象
	public static Connection getConnection(){
		Connection con=null;
		try {
			Class.forName(driver);//加,连
			con=DriverManager.getConnection(url,user,pwd);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;//非void都需要return
	}
	//2.关闭所有方法;有3个参数!,省代码了!!!
	public static void closeAll(ResultSet rs,Statement stmt,Connection con){
		try {
			if(rs!=null){
				rs.close();
			}
			if(stmt!=null){
				stmt.close();
			}
			if(con!=null){
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//3.setParams,用来设置预编译语句对象的?占位符的值;
	public void setParams(PreparedStatement pstmt,Object[]params){
		if(params==null){return; }//return:直接返回，啥也不做;
		try {
			for(int i=0;i<params.length;i++){
				pstmt.setObject(i+1,params[i]);
			}
		} catch (SQLException e) {//有异常,加上去
			e.printStackTrace();
		}
	}
	//4.做公共的更新方法，可以更新所有的基本sql语句;
	public int executeUpdate(String sql,Object[]params){
		//1.声明对象;是将来工作当中省内存;
		Connection con=null;
		PreparedStatement pstmt=null;
		int count=0; //增删改受影响的行数;
		
		try {
			con=this.getConnection();//调用本类的方法；
			pstmt=con.prepareStatement(sql);//建对象:预编译对象,?
			setParams(pstmt,params);//调用设置?的方法，已经写过了!!!
			count=pstmt.executeUpdate();//3.执行;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.closeAll(null, pstmt, con);
		}
		return count;
	}
	//5.执行查询方法;
	public static List executeQuery(String sql, Object[] params) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int colCount = 0;
		ArrayList tableList=new ArrayList();//表集合
		
		try {
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			setParams(pstmt, params);
			rs = pstmt.executeQuery();// 执行查询，结果给rs
			ResultSetMetaData rd = rs.getMetaData();// 获得元数据
			colCount = rd.getColumnCount();
			while (rs.next()) {
				ArrayList rowList = new ArrayList();//行集合
				for (int i = 1; i <= colCount; i++) {
					rowList.add(rs.getString(i));
				}
				tableList.add(rowList);				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(rs,pstmt,con);
		}
		return tableList;
	}
}

DAO接口

import java.util.List;
//针对UserInfo的增删改查接口;
public interface UserInfoDAO {
	public int insertUserInfo(UserInfo user); //saveXXX
	public int updateUserInfo(UserInfo user);
	public int deleteUserInfo(UserInfo user);
	public List<UserInfo>queryUserInfo();//查询学生方法
}

//实现类

import java.util.List;


public class UserInfoDAOImpl extends DaoFactory implements UserInfoDAO {

	@Override
	public int deleteUserInfo(UserInfo user) {
		return 0;
	}

	/* (non-Javadoc)
	 * @see 插入方法，重写
	 */
	@Override
	public int insertUserInfo(UserInfo user) {
		int result=0;
		String sql="";
		Object[]params=;
		result=super.executeUpdate(sql, params);
		return result;
	}

	@Override
	public List<UserInfo> queryUserInfo() {
		String sql="select * from userinfo";
		List list=DaoFactory.executeQuery(sql, null);
		return list;
	}

	@Override
	public int updateUserInfo(UserInfo user) {
		return 0;
	}

}

测试类

package nan;
import java.util.List;

public class Test {
	public static void main(String[] args) {
		
		UserInfoDAO udd=new UserInfoDAOImpl();
		List tableList=udd.queryUserInfo();
		for(int i=0;i<tableList.size();i++){
			List rowList=(List)tableList.get(i);
			//cann't from Object to List,必须强转
			for(int j=0;j<rowList.size();j++){
				System.out.print(rowList.get(j)+"\t");
			}
			System.out.println();//每行完毕后换行
		}
	}
}