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;
      	}
      }
  		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();//查询学生方法
      }
  
 

//实现类

  在线课堂：https://edu.csdn.net/lecturer/1516 

      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;
      	}
      }
  		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();//每行完毕后换行
      		}
      	}
      }
  
 

文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。

原文链接：aaaedu.blog.csdn.net/article/details/52875860