注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

_

_

 
 
 

日志

 
 

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

2014-11-18 17:38:00|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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;

??? }

/**

???? * 增删改【Add、Del、Update】

???? *

???? * @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;

??? }

/**

???? * 增删改【Add、Delete、Update】

???? *

???? * @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);

??? }

}

如果,您认为阅读这篇博客让您有些收获,不妨点击一下右下角的【推荐】。
如果,您希望更容易地发现我的新博客,不妨点击一下左下角的【关注我】。
如果,您对我的博客所讲述的内容有兴趣,请继续关注我的后续博客,我是【虾皮★csAxp】。

本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

  评论这张
 
阅读(278)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017