JDBC处理数据库的有关操作

编辑器加载中...

package com.book.manager.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



/*import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;*/

public class ConnectionData {
    // 主机名
    public static String HOST_DOMAIN = "localhost";// "mgr.vvmoon.com";

    // 驱动程序名
    public static String DRIVE_RNAME = "com.mysql.jdbc.Driver";
    // 数据库用户名
    public static String USER_NAME = "root";
    // 密码
    public static String USER_PWD = "";
    // 数据库名
    public static String DB_NAME = "test";

    public static String ENCODEING = "UTF-8";
    public static Connection connection = null;
    //private static final Log log = LogFactory.getLog(ConnectionData.class);

    /**
     * 构造时取得数据库连接
     */

    public ConnectionData() {
        // this.connection = getConnection();
    }

    // 表名
    // String tableName = "link_resource_template";

    public static void setConnection(Connection connection) {
        ConnectionData.connection = connection;
    }
    
    
    /**
     * 初始化数据连接
     */
    public static void initdata() {
        ConnectionData.HOST_DOMAIN = "localhost";
        ConnectionData.USER_NAME = "root";
        ConnectionData.USER_PWD = "";
        ConnectionData.DB_NAME = "test";
    }

    /**
     * 数据库连接对象
     * 
     * @return
     */
    public static Connection getConnection() {
        try {
            // 加载数据库驱动
            Class.forName(DRIVE_RNAME).newInstance();
            // 联结字符串
            // myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;CharSet=UTF8;
            String URL = "jdbc:mysql://" + HOST_DOMAIN + "/" + DB_NAME
                    + "?user=" + USER_NAME + "&password=" + USER_PWD
                    + "&characterEncoding=utf8";
            //log.debug("连接数据库的URL:" + URL);
            // URL =
            // "jdbc:mysql://mgr.vvmoon.com/test?user=seo&password=express&characterEncoding=utf8";
            Connection conn = DriverManager.getConnection(URL);

            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } catch (InstantiationException e) {
            e.printStackTrace();
            return null;
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            return null;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return null;
        }

    }

    /**
     * 执行查询
     * 
     * @return
     */
    public static ResultSet executeQuery() {
        Connection conn = null != connection ? connection : getConnection();

        String sql = "SELECT * FROM link_resource_template";
        Statement statement;
        try {
            statement = (Statement) conn.prepareStatement(sql);
            ResultSet rs = (ResultSet) statement.executeQuery(sql);
            return rs;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 执行查询
     * 
     * @return
     */
    public static ResultSet executeQuery(String SQL) {
        Connection conn = null != connection ? connection : getConnection();

        if (conn == null || null == SQL || "".equals(SQL)) {
            return null;
        }
        Statement statement;
        try {
            statement = (Statement) conn.prepareStatement(SQL);
            ResultSet rs = (ResultSet) statement.executeQuery(SQL);
            // CloseDataObject(conn, statement, rs);//关闭
            return rs;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }

    }

    /**
     * 执行SQL语句　无参数
     * @throws SQLException 
     */
    public static Integer executeUpdate(String SQL) throws SQLException {
        if (null == SQL || "".equals(SQL)) {
            return 0;
        }
        int rs = 0;
        //log.debug("connection  sql = " + SQL);
        Statement statement = null;
        Connection conn =  getConnection(); //重新获取新连接

        try {
            conn.setAutoCommit(false); // 不　自动提交事务
            statement = (Statement) conn.prepareStatement(SQL);
            rs = statement.executeUpdate(SQL);
            conn.commit();// 手动提交事务
            //log.debug("connection executeUpdate  commit  success;");
        } catch (SQLException e) { 
                conn.rollback(); 
                //log.debug("connection executeUpdate rollback  fail ");
                throw e;
        } finally {
            CloseDataObject(conn, statement, null);// 关闭
        }
        return rs;

    }

    // 释放资源

    public static void CloseDataObject(Connection conn, Statement stat,
            ResultSet rs) throws SQLException {

        if (rs != null) {
            rs.close();
        }

        if (stat != null) {
            stat.close();
        }

        if (conn != null) {
            conn.close();
        }

    }

    public static void main(String[] args) {

        ConnectionData.DB_NAME = "test";
        ConnectionData.HOST_DOMAIN = "mgr.vvmoon.com";
        ConnectionData.USER_NAME = "seo";
        ConnectionData.USER_PWD = "express";
        String sql = "INSERT INTO newtable VALUES(NULL,'小华３','女','你想干dff什么，喂');";

        // Connection conn = getConnection();
        try {
            int s = executeUpdate(sql);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}