Java版DBHelper【简单】(MySQL数据库)

http://www.cnblogs.com/xia520pi/archive/2012/05/22/2513920.html

Java版DBHelper【简单】(MySQL数据库)

1、测试数据库

1.1 数据库数据

  

1.2 数据库结构

  

1.3 数据库脚本

DROP TABLE IF EXISTS `school`.`student`;

 

CREATE TABLE `school`.`student` (

`id` int(11) NOT NULL default '0',

`name` varchar(20) default NULL,

`sex` varchar(10) default NULL,

`age` int(10) default NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `student` VALUES ('201201', '张三', '男', '21');

INSERT INTO `student` VALUES ('201202', '李四', '男', '22');

INSERT INTO `student` VALUES ('201203', '王五', '女', '20');

INSERT INTO `student` VALUES ('201204', '赵六', '男', '21');

INSERT INTO `student` VALUES ('201205', '小红', '女', '19');

INSERT INTO `student` VALUES ('201206', '小明', '男', '22');

2、DBHelper用法

2.1 增加【Add】

    【示例代码

 

package com.hebut.util;

 

public class DBHelperApp {

 

    /**

     * 增加【Add

     */

    public static void main(String[] args) {

        //第一种情况

        String sql1="Insert Into student Values ('201208', '张三', '', '21')";

        System.out.println(DBHelper.executeNonQuery(sql1));

       

        //第二种情况

        String sql2="Insert Into student Values (?,?,?,?)";

        Object[] obj = new Object[]{"201209","张三", "", "21"};

        System.out.println(DBHelper.executeNonQuery(sql2,obj));

    }

}

 

    【运行结果

    1)控制台

 

1

1

    

2)数据库

  

2.2 删除【Del】

    【示例代码

 

package com.hebut.util;

 

public class DBHelperApp {

 

    /**

     * 删除【Del

     */

    public static void main(String[] args) {

        //第一种情况

        String sql1="Delete From student Where id='201202'";

        System.out.println(DBHelper.executeNonQuery(sql1));

       

        //第二种情况

        String sql2="Delete From student Where id=? And name=?";

        Object[] obj = new Object[]{"201208","张三"};

        System.out.println(DBHelper.executeNonQuery(sql2,obj));

    }

 

}

 

    【运行结果

    1)控制台

 

1

1

 

    2)数据库

  

    从图中可以看出id为"201202"和"201208"已经删除。

2.3 修改【Update】

    【示例代码

 

package com.hebut.util;

 

public class DBHelperApp {

 

    /**

     * 修改【Update

     */

    public static void main(String[] args) {

        //第一种情况

        String sql1="Update student Set age='27' Where id='201207'";

        System.out.println(DBHelper.executeNonQuery(sql1));

       

        //第二种情况

        String sql2="Update student Set name=? Where id=?";

        Object[] obj = new Object[]{"张二","201201"};

        System.out.println(DBHelper.executeNonQuery(sql2,obj));

    }

}

 

    【运行结果

    1)控制台

 

1

1

 

    2)数据库

  

  备注:后面的操作都以上面数据为基准。

2.4 查询【Query】

    【示例代码

 

package com.hebut.util;

 

import java.sql.ResultSet;

import java.sql.SQLException;

 

public class DBHelperApp {

 

    /**

     * 查询【Query

     * @throws SQLException

     */

    public static void main(String[] args) throws SQLException {

        //第一种情况

        String sql1="Select * From student";

        //第一步:查询

        ResultSet rs1 = DBHelper.executeQuery(sql1);

        //二步:输出

        while(rs1.next()){

            System.out.println("姓名:"+rs1.getString(2));

        }

        //第三步:关闭

        DBHelper.free(rs1);

       

        //第二种情况

        String sql2="Select * From student Where name=?";

        Object[] obj = new Object[]{"张三"};

        //第一步:查询

        ResultSet rs2 = DBHelper.executeQuery(sql2,obj);

        //第二步:输出

        while(rs2.next()){

            System.out.println("学号:"+rs2.getString(1));

        }

        //第三步:关闭

        DBHelper.free(rs2);

    }

}

 

    【运行结果

 

姓名:张二

姓名:王五

姓名:赵六

姓名:小红

姓名:小明

姓名:张三

姓名:张三

学号:201207

学号:201209

 

2.5 获取查询行数

    【示例代码

 

package com.hebut.util;

 

public class DBHelperApp {

 

    /**

     * 获取查询行数

     */

    public static void main(String[] args) {

        //第一种情况

        String sql1="Select * From student";

        System.out.println(DBHelper.getCount(sql1));

       

        //第二种情况

        String sql2="Select * From student Where name=?";

        Object[] obj = new Object[]{"张三"};

        System.out.println(DBHelper.getCount(sql2,obj));

    }

}

 

    【运行结果

 

7

2

 

2.6 判断记录存在

    【示例代码

 

package com.hebut.util;

 

public class DBHelperApp {

 

    /**

     * 判断记录存在

     */

    public static void main(String[] args) {

        //第一种情况

        String sql1="Select * From student";

        System.out.println(DBHelper.isExist(sql1));

       

        //第二种情况

        String sql2="Select * From student Where name=?";

        Object[] obj = new Object[]{"张五"};

        System.out.println(DBHelper.isExist(sql2,obj));

    }

}

 

    【运行结果

 

true

false

 

3、DBHelper代码

 

package com.hebut.util;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

public final class DBHelper {

 

    // 此方法为获取数据库连接

    public static Connection getConnection() {

        Connection conn = null;

 

        try {

            String driver = "com.mysql.jdbc.Driver"; // 数据库驱动

            String url = "jdbc:MySQL://127.0.0.1:3306/school";// 数据库

            String user = "root"; // 用户名

            String password = "hadoop"; // 密码

            Class.forName(driver); // 加载数据库驱动

            if (null == conn) {

                conn = DriverManager.getConnection(url, user, password);

            }

        } catch (ClassNotFoundException e) {

            System.out.println("Sorry,can't find the Driver!");

            e.printStackTrace();

        } catch (SQLException e) {

            e.printStackTrace();

        } catch (Exception e) {

            e.printStackTrace();

        }

        return conn;

    }

 

    /**

     * 增删改【AddDelUpdate

     *

     * @param sql

     * @return int

     */

    public static int executeNonQuery(String sql) {

        int result = 0;

        Connection conn = null;

        Statement stmt = null;

 

        try {

            conn = getConnection();

            stmt = conn.createStatement();

            result = stmt.executeUpdate(sql);

        } catch (SQLException err) {

            err.printStackTrace();

            free(null, stmt, conn);

        } finally {

            free(null, stmt, conn);

        }

 

        return result;

    }

 

    /**

     * 增删改【AddDeleteUpdate

     *

     * @param sql

     * @param obj

     * @return int

     */

    public static int executeNonQuery(String sql, Object... obj) {

        int result = 0;

        Connection conn = null;

        PreparedStatement pstmt = null;

 

        try {

            conn = getConnection();

            pstmt = conn.prepareStatement(sql);

 

            for (int i = 0; i < obj.length; i++) {

                pstmt.setObject(i + 1, obj[i]);

            }

 

            result = pstmt.executeUpdate();

        } catch (SQLException err) {

            err.printStackTrace();

            free(null, pstmt, conn);

        } finally {

            free(null, pstmt, conn);

        }

        return result;

    }

 

    /**

     * 查【Query

     *

     * @param sql

     * @return ResultSet

     */

    public static ResultSet executeQuery(String sql) {

        Connection conn = null;

        Statement stmt = null;

        ResultSet rs = null;

 

        try {

            conn = getConnection();

            stmt = conn.createStatement();

            rs = stmt.executeQuery(sql);

        } catch (SQLException err) {

            err.printStackTrace();

            free(rs, stmt, conn);

        }

 

        return rs;

    }

 

    /**

     * 查【Query

     *

     * @param sql

     * @param obj

     * @return ResultSet

     */

    public static ResultSet executeQuery(String sql, Object... obj) {

        Connection conn = null;

        PreparedStatement pstmt = null;

        ResultSet rs = null;

 

        try {

            conn = getConnection();

            pstmt = conn.prepareStatement(sql);

           

            for (int i = 0; i < obj.length; i++) {

                pstmt.setObject(i + 1, obj[i]);

            }

           

            rs = pstmt.executeQuery();

        } catch (SQLException err) {

            err.printStackTrace();

            free(rs, pstmt, conn);

        }

 

        return rs;

    }

 

    /**

     * 判断记录是否存在

     *

     * @param sql

     * @return Boolean

     */

    public static Boolean isExist(String sql) {

        ResultSet rs = null;

 

        try {

            rs = executeQuery(sql);

            rs.last();

            int count = rs.getRow();

            if (count > 0) {

                return true;

            } else {

                return false;

            }

        } catch (SQLException err) {

            err.printStackTrace();

            free(rs);

            return false;

        } finally {

            free(rs);

        }

    }

 

    /**

     * 判断记录是否存在

     *

     * @param sql

     * @return Boolean

     */

    public static Boolean isExist(String sql, Object... obj) {

        ResultSet rs = null;

 

        try {

            rs = executeQuery(sql, obj);

            rs.last();

            int count = rs.getRow();

            if (count > 0) {

                return true;

            } else {

                return false;

            }

        } catch (SQLException err) {

            err.printStackTrace();

            free(rs);

            return false;

        } finally {

            free(rs);

        }

    }

 

    /**

     * 获取查询记录的总行数

     *

     * @param sql

     * @return int

     */

    public static int getCount(String sql) {

        int result = 0;

        ResultSet rs = null;

 

        try {

            rs = executeQuery(sql);

            rs.last();

            result = rs.getRow();

        } catch (SQLException err) {

            free(rs);

            err.printStackTrace();

        } finally {

            free(rs);

        }

 

        return result;

    }

 

    /**

     * 获取查询记录的总行数

     *

     * @param sql

     * @param obj

     * @return int

     */

    public static int getCount(String sql, Object... obj) {

        int result = 0;

        ResultSet rs = null;

 

        try {

            rs = executeQuery(sql, obj);

            rs.last();

            result = rs.getRow();

        } catch (SQLException err) {

            err.printStackTrace();

        } finally {

            free(rs);

        }

 

        return result;

    }

 

    /**

     * 释放【ResultSet】资源

     *

     * @param rs

     */

    public static void free(ResultSet rs) {

        try {

            if (rs != null) {

                rs.close();

            }

        } catch (SQLException err) {

            err.printStackTrace();

        }

    }

 

    /**

     * 释放【Statement】资源

     *

     * @param st

     */

    public static void free(Statement st) {

        try {

            if (st != null) {

                st.close();

            }

        } catch (SQLException err) {

            err.printStackTrace();

        }

    }

 

    /**

     * 释放【Connection】资源

     *

     * @param conn

     */

    public static void free(Connection conn) {

        try {

            if (conn != null) {

                conn.close();

            }

        } catch (SQLException err) {

            err.printStackTrace();

        }

    }

 

    /**

     * 释放所有数据资源

     *

     * @param rs

     * @param st

     * @param conn

     */

    public static void free(ResultSet rs, Statement st, Connection conn) {

        free(rs);

        free(st);

        free(conn);

    }

}

posted on 2014-03-28 16:54  ttjava  阅读(503)  评论(0编辑  收藏  举报