Common Dbutils是操作數(shù)據(jù)庫的組件，對傳統(tǒng)操作數(shù)據(jù)庫的類進(jìn)行二次封裝，可以把結(jié)果集轉(zhuǎn)化成List。 補(bǔ)充一下，傳統(tǒng)操作數(shù)據(jù)庫的類指的是JDBC（java database connection：java數(shù)據(jù)庫連接，java的數(shù)據(jù)庫操作的基礎(chǔ)API。）。 DBUtils是java編程中的數(shù)據(jù)庫操作實(shí)用工具，小巧簡單實(shí)用。有興趣的話可以到官網(wǎng)下載：http://commons.apache.org/dbutils/  下面的工具包正是符合了Common Dbutils 的思想

package com.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
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.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class JdbcUtils
{

    // 表示定義數(shù)據(jù)庫的用戶名
    private final String USERNAME = "root";
    // 定義數(shù)據(jù)庫的密碼
    private final String PASSWORD = "admin";
    // 定義數(shù)據(jù)庫的驅(qū)動(dòng)信息
    private final String DRIVER = "com.mysql.jdbc.Driver";
    // 定義訪問數(shù)據(jù)庫的地址
    private final String URL = "jdbc:mysql://localhost:3306/mydb";
    // 定義數(shù)據(jù)庫的鏈接
    private Connection connection;
    // 定義sql語句的執(zhí)行對象
    private PreparedStatement pstmt;
    // 定義查詢返回的結(jié)果集合
    private ResultSet resultSet;

    public JdbcUtils()
    {
        try
        {
            Class.forName(DRIVER);
            System.out.println("注冊驅(qū)動(dòng)成功!!");
        }
        catch (Exception e)
        {
            // TODO: handle exception
        }
    }

    // 定義獲得數(shù)據(jù)庫的鏈接
    public Connection getConnection()
    {
        try
        {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            System.out.println("數(shù)據(jù)庫連接成功");
        }
        catch (Exception e)
        {
            // TODO: handle exception
        }
        return connection;
    }

    
    public boolean updateByPreparedStatement(String sql, Listparams)
            throws SQLException
    {
        boolean flag = false;
        int result = -1;// 表示當(dāng)用戶執(zhí)行添加刪除和修改的時(shí)候所影響數(shù)據(jù)庫的行數(shù)
        pstmt = connection.prepareStatement(sql);
        int index = 1;
        // 填充sql語句中的占位符
        if (params != null && !params.isEmpty())
        {
            for (int i = 0; i < params.size(); i++)
            {
                pstmt.setObject(index++, params.get(i));
            }
        }
        result = pstmt.executeUpdate();
        flag = result > 0 ? true : false;
        return flag;
    }

    
    public Map findSimpleResult(String sql, Listparams)
            throws SQLException
    {
        Map map = new HashMap();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty())
        {
            for (int i = 0; i < params.size(); i++)
            {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();// 返回查詢結(jié)果
        // 獲取此 ResultSet 對象的列的編號(hào)、類型和屬性。
        ResultSetMetaData metaData = resultSet.getMetaData();
        int col_len = metaData.getColumnCount();// 獲取列的長度
        while (resultSet.next())// 獲得列的名稱
        {
            for (int i = 0; i < col_len; i++)
            {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null)// 列的值沒有時(shí)，設(shè)置列值為“”
                {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
        }
        return map;
    }

    
    public List> findMoreResult(String sql,
            Listparams) throws SQLException
    {
        List> list = new ArrayList>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty())
        {
            for (int i = 0; i < params.size(); i++)
            {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        while (resultSet.next())
        {
            Map map = new HashMap();
            for (int i = 0; i < cols_len; i++)
            {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null)
                {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
            list.add(map);
        }
        return list;
    }

    
    public T findSimpleRefResult(String sql, Listparams,
            Class cls) throws Exception
    {
        T resultObject = null;
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty())
        {
            for (int i = 0; i < params.size(); i++)
            {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        while (resultSet.next())
        {
            // 通過反射機(jī)制創(chuàng)建實(shí)例
            resultObject = cls.newInstance();
            for (int i = 0; i < cols_len; i++)
            {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null)
                {
                    cols_value = "";
                }
                // 返回一個(gè) Field 對象，該對象反映此 Class 對象所表示的類或接口的指定已聲明字段。
               Field field = cls.getDeclaredField(cols_name);
                field.setAccessible(true);// 打開javabean的訪問private權(quán)限
                field.set(resultObject, cols_value);// 為resultObject對象的field的屬性賦值
            } //上面的兩行紅題字就是要求實(shí)體類中的屬性名一定要和數(shù)據(jù)庫中的字段名一定要嚴(yán)//格相同（包括大小寫）,而oracle數(shù)據(jù)庫中一般都是大寫的，如何讓oracle區(qū)分大小寫，請看博///文：http://blog.sina.com.cn/s/blog_7ffb8dd501013xkq.html
        }
        return resultObject;
    }

    
    public List findMoreRefResult(String sql, Listparams,
            Class cls) throws Exception
    {
        List list = new ArrayList();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty())
        {
            for (int i = 0; i < params.size(); i++)
            {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        while (resultSet.next())
        {
            T resultObject = cls.newInstance();
            for (int i = 0; i < cols_len; i++)
            {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null)
                {
                    cols_value = "";
                }
                Field field = cls.getDeclaredField(cols_name);
                field.setAccessible(true);
                field.set(resultObject, cols_value);
            }
            list.add(resultObject);
        }
        return list;
    }

    
    public void releaseConn()
    {
        if (resultSet != null)
        {
            try
            {
                resultSet.close();
            }
            catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (pstmt != null)
        {
            try
            {
                pstmt.close();
            }
            catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (connection != null)
        {
            try
            {
                connection.close();
            }
            catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    
    public static void writeProperties(String url, String user, String password)
    {
        Properties pro = new Properties();
        FileOutputStream fileOut = null;
        try
        {
            fileOut = new FileOutputStream("Config.ini");
            pro.put("url", url);
            pro.put("user", user);
            pro.put("password", password);
            pro.store(fileOut, "My Config");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (fileOut != null)
                    fileOut.close();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }
        }
    }

    
    public static List readProperties()
    {
        List list = new ArrayList();
        Properties pro = new Properties();
        FileInputStream fileIn = null;
        try
        {
            fileIn = new FileInputStream("Config.ini");
            pro.load(fileIn);
            list.add(pro.getProperty("url"));
            list.add(pro.getProperty("user"));
            list.add(pro.getProperty("password"));
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (fileIn != null)
                    fileIn.close();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }
        }
        return list;
    }

    
    public static void main(String[] args)
    {
        // TODO Auto-generated method stub
        JdbcUtils jdbcUtils = new JdbcUtils();
        jdbcUtils.getConnection();
        // String sql = "insert into userinfo(username,pswd) values(?,?)";
        // Listparams = new ArrayList();
        // params.add("rose");
        // params.add("123");
        // try {
        // boolean flag = jdbcUtils.updateByPreparedStatement(sql, params);
        // System.out.println(flag);
        // } catch (SQLException e) {
        // // TODO Auto-generated catch block
        // e.printStackTrace();
        // }
        String sql = "select * from userinfo ";
        // Listparams = new ArrayList();
        // params.add(1);
        
    }

}

標(biāo)簽： Mysql 權(quán)限 數(shù)據(jù)庫

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請與原作者聯(lián)系。