1、定义该类需要用到的一些属性与实例
private static final String DRIVERCLASS="com.microsoft.jdbc.sqlserver.SQLServerDriver";
protect final String url="jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=db_JSDQ10";
protect final String user="sa";
protect final String password="";
protect Connection conn;
2、负责加裁数据库驱动的静态方法
static{
try{
Class.forName(DRIVERCLASS);
}catch(ClassNotFoundException e){
System.out.println(" Load Class error.");
e.printStackTrace();
}
}
3、建立数据库的连接并设置自动提交模式
public boolean openConn(boolean autoCommint){
boolean isOpen=true;
try{
conn=DriverManager.getConnection(url, user, password);
if(!autoCommit)
conn.setAutoCommit(false);
}catch(SQLException e){
isOpen=false;
System.out.println(" Create connection error.");
e.printStackTrace();
}
}
4、关闭数据库连接
public boolean closeConn(){
boolean isCommit=true;
try{
conn.commit();
}catch(SQLException e){
isCommit=false;
System.out.println("Commit error.");
e.printStackTrace();
try{
conn.rollback();
}catch(SQLException e1){
System.out.println("Rollback error.");
e1.printStackTrace();
}
}final{
try{
stmt.close();
conn.close();
}catch(SQLException e){
System.out.println("Close Connection error.");
e.printStackTrace();
}
return isCommit;
}
}
5、获取数据库连接
public class TestJDBC extends JDBC{
public List<Object[]> selectBySQL(String sql){
List<Object[]> notes=new ArrayList<Object[]>();
this.openConn(true);
try{
this.stmt=conn.createStatement();
this.rs=stmt.executeQuery(sql);
int l=rs.getMetaData().getColumnCount();
while(rs.next()){
Object[] note=new Object[];
for(int i=0; i<l; i++){
note[i]=rs.getObject(i+1);
}
notes.add(note);
}
this.rs.close();
this.stmt.close();
}catch(SQLException e){
e.printStackTrace();
}
this.closeConn();
return notes;
}
}
调用例:
List<Object[]> notes=testJDBC.selectBySQL("select * from tb_test_join");
for(int m=0; m<notes.size(); m++){
out.println("<tr align='center' bgcolor='#FFCC99'>");
Object[] note=notes.get(m);
for(int n=0; n<note.length; n++){
out.println("<td>"+note[n]+"</td>");
}
out.println("</tr>");
}