基本的資料庫增刪改查

整理電腦無意間發現了這段程式碼，想起了自己剛入門的時候。發表出來，紀念下。

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

public class DBConn
{
    private String url = "jdbc:oracle:thin:@localhost:1521:orcl";

    private String cls = "oracle.jdbc.driver.OracleDriver";

    private String username = "system";

    private String password = "manager";

    //三屬性、四方法
    //三大核心介面
    private Connection conn = null;

    private PreparedStatement pstmt = null;

    private ResultSet rs = null;

    //四方法
    /**
     * 1 得到資料庫連線
     */
    public void getConnection()
    {
        try
        {
            Class.forName(cls);
            conn = DriverManager.getConnection(url, username, password);
        }
        catch (ClassNotFoundException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 封閉資料庫連線
     */
    public void closeConn()
    {
        if (null != rs)
        {
            try
            {
                rs.close();
            }
            catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (null != pstmt)
        {
            try
            {
                pstmt.close();
            }
            catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (null != conn)
        {
            try
            {
                conn.close();
            }
            catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    /**
     * 增刪改語句
     */
    public int execOther(final String strSQL, Object[] params)
    {
        this.getConnection();
        System.out.println(strSQL);
        try
        {
            pstmt = conn.prepareStatement(strSQL);
            for (int i = 0; i < params.length; i++)
            {
                pstmt.setObject(i + 1, params[i]);
            }
            int affectRows = pstmt.executeUpdate();
            return affectRows;
        }
        catch (SQLException e1)
        {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        return 0;
    }

    /**
     * 查詢語句
     */
    public ResultSet execQuery(final String strSQL, Object[] params)
    {
        this.getConnection();
        System.out.println(strSQL);
        try
        {
            pstmt = conn.prepareStatement(strSQL);
            for (int i = 0; i < params.length; i++)
            {
                pstmt.setObject(i + 1, params[i]);
            }
            rs = pstmt.executeQuery();
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;
    }
}

mian函式
public static void main(String[] args)
    {
        DBUtil dbUtil = new DBUtil();
        String name = "tom";
        String password = "tom";
        Object[] params = new Object[] {password};
        String strSQL = "select * from person where password=?";
        ResultSet rs = dbUtil.execQuery(strSQL, params);
        try
        {
            while (rs.next())
            {
                System.out.println(rs.getInt(1));
            }
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

基本的資料庫增刪改查

相關文章