Java版DBHelper【简单】（MySQL数据库） -----超级详细

蔚落 2022-08-10 11:49 72阅读 0赞

## 刚在网上找到的一篇写的非常详细，不得不转！[原地址][Link 1] ##

## 1、测试数据库 ##

### 1.1 数据库数据 ###

![201205222147564280.png][]

### 1.2 数据库结构 ###

![201205222147565327.png][]

### 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）数据库

![201205222147576656.png][]

### 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）数据库

![201205222147576308.png][]

从图中可以看出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）数据库

![201205222147572687.png][]

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

### 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); 
> 
>     \} 
> 
> \}

[Link 1]: http://www.cnblogs.com/xia520pi/archive/2012/05/22/2513920.html
[201205222147564280.png]: /images/20220810/68bd0254b10a4715ae59e007226ab7b6.png
[201205222147565327.png]: /images/20220810/e1556650901045839691e9293582fbd2.png
[201205222147576656.png]: /images/20220810/a30326ed513f4f05ba9279c3a2299681.png
[201205222147576308.png]: /images/20220810/6dc3648ddca944a38c08ff58b0365228.png
[201205222147572687.png]: /images/20220810/33a875c5cde3481197bbd0f0c650fee5.png