JAVA使用JDBC操作数据库

/**
 * @author dingyaodong
 * @date 2015年11月20日
 */
package com.bailiangroup.openapi.console.web.controller;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Properties;

import javax.inject.Inject;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.bailiangroup.openapi.modules.console.domain.entity.ApiSysUser;
import com.bailiangroup.openapi.modules.console.service.ApiContextService;
import com.bailiangroup.osp.common.config.Global;


/**
 * @author dingyaodong
 * @date 2015年11月20日
 */
@Controller
@RequestMapping(value="${adminPath}/jdbcCon")
public class JdbcConController {

    @Inject
    private static ApiContextService apiContextService;

    private static String openapi_jdbc_driver=Global.getConfig("openapi.jdbc.driver");;

    private static String openapi_jdbc_url=Global.getConfig("openapi.jdbc.url");;

    private static String openapi_jdbc_username=Global.getConfig("openapi.jdbc.username");;

    private static String openapi_jdbc_password=Global.getConfig("openapi.jdbc.password");;

    public static void main(String[] args) {
        //publicSQLQuery("select * from API_USERS where USER_NAME='dyd'");
        update("update API_USERS set POWER_STATUS='' where USER_NAME='dyd'");
    }

    private static  Connection getConn(){
        Connection conn = null;
        try {
            Class.forName( openapi_jdbc_driver); //classLoader,加载对应驱动
            conn = (Connection) DriverManager.getConnection(openapi_jdbc_url, openapi_jdbc_username, openapi_jdbc_password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    @ResponseBody
    @RequestMapping(value ="SQLQuery")
    public static Object publicSQLQuery(String sql) throws Exception{
        Object  result="";
        Connection conn =getConn();
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            ResultSetMetaData md = rs.getMetaData(); 
            StringBuffer buffer=new StringBuffer();
            buffer.append("<table style='border: solid thin blue' width='100%' border='2' cellpadding='2' cellspacing='2'>");
            int col = rs.getMetaData().getColumnCount();
            buffer.append("<tr>");
            for (int i = 1; i <= col; i++) {
                buffer.append("<td>"+md.getColumnName(i)+"</td>");
            }
            buffer.append("</tr>");
            while (rs.next()) {
                buffer.append("<tr>");
                for (int i = 1; i <= col; i++) {
                    buffer.append("<td>"+rs.getString(i)+"</td>");
                    System.out.println();
                 }
                buffer.append("</tr>"); 
            }

            buffer.append("</table>");
            result=buffer;
            System.out.println("resutl: "+result );
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
            return "message:"+e.getMessage()+";errorCode:"+e.getErrorCode();
        }
        return result;
    }

    @ResponseBody
    @RequestMapping(value ="update")
    public static int update(String sql) {
        Connection conn = getConn();
        int i = 0;
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            System.out.println("resutl: " + i); 
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @RequestMapping(value = {"list", ""})
    public String list(HttpServletResponse response, Model model){
        return "modules/sys/jdbcEdit";
    }
}