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();//查询学生方法
}
//实现类
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();//每行完毕后换行
}
}
}