java操作数据库

首先，创建一个DBConnection类（链接数据库的类）

public class DBConnection {
	public Connection getConnection()
	{
     Connection conn=null;
     //链接SQLServer2000    
     //String CLASSFORNAME="com.microsoft.jdbc.sqlserver.SQLServerDriver";	
    // String SERVANDDB="jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=tempdb";    
     //连接SQLServer2005
    String CLASSFORNAME="com.microsoft.sqlserver.jdbc.SQLServerDriver";	
    String SERVANDDB="jdbc:sqlserver://localhost:1433;DatabaseName=jmorder";    
  //  String SERVANDDB="jdbc:sqlserver://219.229.249.71;DatabaseName=jxpro_manage"; 
     String USER="sa";
	 String PWD="123";
	//String PWD="jxedu";	 
	try
	{
	  Class.forName(CLASSFORNAME);
      conn=DriverManager.getConnection(SERVANDDB,USER,PWD);	  
	}catch(Exception ex)
	{
		ex.printStackTrace();
	}
	 return conn;
	}	   
}

然后创建一个DB类（操作数据的类），写操作数据库的方法execSelect执行查询语句

public ResultSet execSelect(String sql) {//执行查询语句,返回一个结果集

		try {
		 if(conn!=null){
			stmt = conn.createStatement(
					java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
					java.sql.ResultSet.CONCUR_READ_ONLY);
			if(stmt!=null){
			  rs = stmt.executeQuery(sql);
			}
		  }
		} catch (SQLException se) {          
            System.out.print(se.getMessage());
		}
		return rs;
	}

执行修改语句：

public String execUpdate(String sql) {
		Statement stmt = null;
		String flag = "";
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			if(stmt!=null)
			{
				stmt.executeUpdate(sql);
			    flag = "success";
			}
		} catch (SQLException se) {
			System.out.print(se.getMessage());

			flag = "error";
		}

		return flag;
	}

执行数据库备份与还原语句

public String exec(String sql) {
		Statement stmt = null;
		String flag = "";
		try {
			stmt = conn.createStatement();
			stmt.execute(sql);
			flag = "success";
		} catch (SQLException se) {
			System.out.print(se.getMessage());

			flag = "error";
		}
		return flag;
	}

关闭conn

public void setConnClose() {
		try {

			conn.close();
			conn=null;
		} catch (Exception e) {
		}
	}

Tomcat服务器关闭的方法

public void freeCon() {
		try {
			if (rs != null){
				rs.close();
			    rs=null;	
			}
			if (ps != null)
			{
				ps.close();
			    ps=null;
			}
			if (stmt != null)
			{
				stmt.close();
				stmt=null;
			}
			if (conn != null)
			{
				conn.close();
			   conn=null;
			 }
		} catch (SQLException ex) {
		}
	}

将ResultSet转换Json类型

 public String resultSetToJson(ResultSet rs) throws SQLException,JSONException
	    {
		
	       // json数组
	       JSONArray array = new JSONArray();	  
	       // 获取列数
	       ResultSetMetaData metaData = rs.getMetaData();
	       int columnCount = metaData.getColumnCount();	      
	       // 遍历ResultSet中的每条数据
	        while (rs.next()) {
	            JSONObject jsonObj = new JSONObject();	           
	            // 遍历每一列
	            for (int i = 1; i <= columnCount; i++) {
	                String columnName =metaData.getColumnLabel(i);
	                String value = rs.getString(columnName);
	                jsonObj.put(columnName, value);
	               // System.out.println(jsonObj.toString());
	            } 
	            array.add(jsonObj); 
	            System.out.println(array);
	          
	        }
	      
	       return array.toString();
	    }