java通过JDBC操作数据库

一、什么是JDBC

JDBC即Java DataBase Connective,是由JAVA提供的一组成平台无关的数据库操作标准。通过这个规范,各数据库服务商实现各自的驱动,用来管理和操作数据库。

JDBC的作用

二、使用JDBC操作数据库

1.JDBC操作数据库的主要步骤
  • (1)加载数据库驱动
  • (2)建立数据库连接
  • (3)预备需要执行的SQL语句
  • (4)执行SQL语句并获取结果,对结果进行处理
  • (5)释放资源(关闭连接)

JDBC操作数据库步骤

2.使用JDBC操作数据库实例
(0) 准备工作
  • 建立一张数据库表tag
  1. CREATE TABLE `tag` (
  2. `tag_id` int(12) NOT NULL AUTO_INCREMENT COMMENT '标题ID',
  3. `tag_name` varchar(50) NOT NULL COMMENT '标签名称',
  4. `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态',
  5. `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
  6. PRIMARY KEY (`tag_id`),
  7. UNIQUE KEY `idx_tname` (`tag_name`) USING BTREE
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='标签表';
  • 下载MySQL的驱动程序

下载地址为:https://mvnrepository.com/artifact/mysql/mysql-connector-java

(1)加载驱动程序
  • 通过Class.forName反射来实现加载驱动
  1. Class.forName("com.mysql.cj.jdbc.Driver");

注意点:

(2)连接数据库服务器,取得连接对象
  • 通过DriverManager.getConnection来连接
  1. String url = "jdbc:mysql://127.0.0.1:3306/test";
  2. String username = "root";
  3. String password = "123456";
  4. connection = DriverManager.getConnection(url, username, password);

参数说明:

  • 1.连接第一个参数url即连接的数据库服务器地址,其格式为:jdbc:[数据库类型名称]://[数据库服务器IP地址或域名地址]:[数据库服务端口号]/数据库库名称
  • 2.连接的第二个参数username即连接使用的用户名
  • 3.连接的第三个参数password即连接使用的密码
(3)预处理SQL语句
  • 通过PrepareStatement对象的prepare方法来实现预处理
  • 通过PrepareStatement对象的setXX方法来实现参数绑定
  1. String sql = "INSERT INTO tag(tag_name, status, create_time) VALUES (?, ?, ?)";
  2. statement = connection.prepareStatement(sql);
  3. statement.setString(1, "JAVA");
  4. statement.setShort(2, Short.valueOf("1"));
  5. statement.setLong(3, System.currentTimeMillis());
(4)执行SQL语句,获取结果
  • 1.查询类语句使用 executeQuery,得到一个ResultSet对象
  • 2.写操作类语句使用 executeUpdate,得到一个影响的行数,来确定执行结果
  1. int affected = statement.executeUpdate();
  2. if (affected > 0) {
  3. System.out.println("执行成功");
  4. } else {
  5. System.out.println("执行失败");
  6. }
(5)关闭连接,释放资源
  • 建议将释放资源步骤放到finally中,是因为前面的操作可能会出现异常,而导致资源没有及时释放
  1. if (statement != null) {
  2. try {
  3. statement.close();
  4. } catch (Exception ex) {
  5. System.out.println("关闭结果流失败");
  6. }
  7. }
  8. if (connection != null) {
  9. try {
  10. connection.close();
  11. } catch (SQLException e) {
  12. System.out.println("关闭连接流失败");
  13. e.printStackTrace();
  14. }
  15. }
3.使用JDBC实现一个简易版的数据库操作类
  1. package main.java.com.shixinke.java.demo.jdbc;
  2. import java.sql.*;
  3. import java.util.ArrayList;
  4. import java.util.HashMap;
  5. import java.util.List;
  6. import java.util.Map;
  7. class MySQL {
  8. private Connection connection;
  9. private PreparedStatement statement;
  10. private String sql;
  11. public MySQL(String url, String username, String password) {
  12. this.connect(url, username, password);
  13. }
  14. public MySQL(String host, int port, String database, String username, String password) {
  15. String url = "jdbc:mysql://"+ host + ":" +port + "/" + database;
  16. this.connect(url, username, password);
  17. }
  18. /**
  19. * 连接数据库
  20. * @param url
  21. * @param username
  22. * @param password
  23. * @return
  24. */
  25. public Connection connect(String url, String username, String password) {
  26. try {
  27. Class.forName("com.mysql.cj.jdbc.Driver");
  28. connection = DriverManager.getConnection(url, username, password);
  29. } catch (ClassNotFoundException ex) {
  30. System.out.println("驱动未找到:"+ex.getMessage());
  31. } catch (SQLException ex) {
  32. System.out.println("数据库连接失败:"+ex.getMessage());
  33. }
  34. return connection;
  35. }
  36. /**
  37. * 查询操作
  38. * @param tableName 表名
  39. * @param condition 绑定的参数
  40. * @return
  41. */
  42. public List<Map<String, Object>> query(String tableName, Map<String, Object> condition) {
  43. ResultSet resultSet = null;
  44. List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>(0);
  45. try {
  46. StringBuffer sql = new StringBuffer("SELECT * FROM " + tableName + " WHERE ");
  47. for (String key : condition.keySet()) {
  48. sql.append(" "+ key + "= ? AND");
  49. }
  50. String sqlStr = sql.toString().substring(0, sql.length()- 4);
  51. statement = connection.prepareStatement(sqlStr);
  52. int i = 1;
  53. for (Object o : condition.values()) {
  54. statement.setObject(i, o);
  55. i ++;
  56. }
  57. this.sql = sqlStr;
  58. resultSet = statement.executeQuery();
  59. ResultSetMetaData md = resultSet.getMetaData();// 获得结果集结构信息(元数据)
  60. int columnCount = md.getColumnCount();// ResultSet列数
  61. while (resultSet.next()) {
  62. Map<String, Object> line = new HashMap<>(columnCount);
  63. for (int t = 1 ; t <= columnCount; t ++) {
  64. line.put(md.getColumnName(t).toLowerCase(), resultSet.getObject(t));
  65. }
  66. dataList.add(line);
  67. }
  68. } catch (SQLException ex) {
  69. System.out.println("数据库查询失败:"+ex.getMessage());
  70. ex.printStackTrace();
  71. } finally {
  72. if (resultSet != null) {
  73. try {
  74. resultSet.close();
  75. } catch (SQLException e) {
  76. e.printStackTrace();
  77. }
  78. }
  79. close();
  80. }
  81. return dataList;
  82. }
  83. /**
  84. * 插入操作
  85. * @param tableName 表名
  86. * @param data 插入的数据Map
  87. * @return
  88. */
  89. public int insert(String tableName, Map<String, Object> data) {
  90. int count = 0;
  91. try {
  92. StringBuffer fields = new StringBuffer();
  93. StringBuffer placeholder = new StringBuffer();
  94. for (String key : data.keySet()) {
  95. fields.append(key + ",");
  96. placeholder.append("?"+",");
  97. }
  98. String sql = "INSERT INTO " + tableName + "(" + fields.toString().substring(0, fields.length() -1) + ") VALUES (" + placeholder.toString().substring(0, placeholder.length()-1)+ ")";
  99. statement = connection.prepareStatement(sql);
  100. this.sql = sql;
  101. int i = 1;
  102. for (Object value : data.values()) {
  103. statement.setObject(i, value);
  104. i ++;
  105. }
  106. count = statement.executeUpdate();
  107. } catch (SQLException ex) {
  108. System.out.println("数据库查询失败:"+ex.getMessage());
  109. ex.printStackTrace();
  110. } finally {
  111. close();
  112. }
  113. return count;
  114. }
  115. /**
  116. * 更新操作
  117. * @param tableName 表名
  118. * @param field 更新的条件字段
  119. * @param data 更新的数据Map
  120. * @return
  121. */
  122. public int update(String tableName, String field, Map<String, Object> data) {
  123. int count = 0;
  124. try {
  125. StringBuffer sql = new StringBuffer("UPDATE "+tableName+" SET ");
  126. int keyIndex = 0;
  127. int i = 0;
  128. for (String key: data.keySet()) {
  129. sql.append(key +" = ?,");
  130. if (field.equals(key)) {
  131. keyIndex = i;
  132. }
  133. i ++;
  134. }
  135. String sqlStr = sql.toString().substring(0, sql.length() - 1) + " WHERE " + field + "= ?";
  136. this.sql = sqlStr;
  137. statement = connection.prepareStatement(sqlStr);
  138. int j = 0;
  139. for (Object o : data.values()) {
  140. statement.setObject(j + 1, o);
  141. if (j == keyIndex) {
  142. statement.setObject(data.size() +1, o);
  143. }
  144. j ++;
  145. }
  146. count = statement.executeUpdate();
  147. } catch (SQLException ex) {
  148. System.out.println("数据库更新失败:"+ex.getMessage());
  149. ex.printStackTrace();
  150. } finally {
  151. close();
  152. }
  153. return count;
  154. }
  155. /**
  156. * 删除
  157. * @param tableName 表名
  158. * @param condition 删除条件字段
  159. * @return
  160. */
  161. public int delete(String tableName, Map<String, Object> condition) {
  162. int count = 0;
  163. try {
  164. StringBuffer sqlBuffer = new StringBuffer("DELETE FROM " + tableName + " WHERE ");
  165. for (String k : condition.keySet()) {
  166. sqlBuffer.append(" " + k + "=" + "? and");
  167. }
  168. String sql = sqlBuffer.toString().substring(0, sqlBuffer.length() - 4);
  169. statement = connection.prepareStatement(sql);
  170. this.sql = sql;
  171. int i = 1;
  172. for (Object o : condition.values()) {
  173. statement.setObject(i, o);
  174. i++;
  175. }
  176. count = statement.executeUpdate();
  177. } catch (SQLException ex) {
  178. System.out.println("数据库查询失败:"+ex.getMessage());
  179. ex.printStackTrace();
  180. } finally {
  181. close();
  182. }
  183. return count;
  184. }
  185. public void close() {
  186. if (statement != null) {
  187. try {
  188. statement.close();
  189. } catch (Exception ex) {
  190. System.out.println("关闭结果流失败");
  191. }
  192. }
  193. if (connection != null) {
  194. try {
  195. connection.close();
  196. } catch (SQLException e) {
  197. System.out.println("关闭连接流失败");
  198. e.printStackTrace();
  199. }
  200. }
  201. }
  202. public String getLastSql() {
  203. return this.sql;
  204. }
  205. }
  206. public class MysqlDemo {
  207. public static void insert() {
  208. MySQL mysql = new MySQL("127.0.0.1", 3306, "test", "root", "123456");
  209. Map<String, Object> insertData = new HashMap<String, Object>(3);
  210. insertData.put("tag_name", "MySQL");
  211. insertData.put("status", 1);
  212. insertData.put("create_time", System.currentTimeMillis());
  213. int count = mysql.insert("tag", insertData);
  214. if (count > 0) {
  215. System.out.println("插入成功");
  216. } else {
  217. System.out.println("插入失败:"+mysql.getLastSql());
  218. }
  219. }
  220. public static void update() {
  221. MySQL mysql = new MySQL("127.0.0.1", 3306, "test", "root", "123456");
  222. Map<String, Object> updateData = new HashMap<String, Object>(3);
  223. updateData.put("tag_name", "Zookeeper");
  224. updateData.put("status", 2);
  225. updateData.put("tag_id", 2);
  226. int count = mysql.update("tag", "tag_id", updateData);
  227. if (count > 0) {
  228. System.out.println("更新成功");
  229. } else {
  230. System.out.println("更新失败:"+mysql.getLastSql());
  231. }
  232. }
  233. public static void query() {
  234. MySQL mysql = new MySQL("127.0.0.1", 3306, "test", "root", "123456");
  235. Map<String, Object> condition = new HashMap<String, Object>(1);
  236. condition.put("tag_name", "JAVA");
  237. List<Map<String, Object>> dataList = mysql.query("tag", condition);
  238. if (dataList != null && dataList.size() > 0) {
  239. for (int i = 0; i< dataList.size(); i++) {
  240. System.out.println(dataList.get(i));
  241. }
  242. } else {
  243. System.out.println("没有满足条件的数据");
  244. }
  245. }
  246. public static void delete() {
  247. MySQL mysql = new MySQL("127.0.0.1", 3306, "test", "root", "123456");
  248. Map<String, Object> condition = new HashMap<String, Object>(3);
  249. condition.put("tag_id", 2);
  250. int count = mysql.delete("tag", condition);
  251. if (count > 0) {
  252. System.out.println("删除成功");
  253. } else {
  254. System.out.println("删除失败:"+mysql.getLastSql());
  255. }
  256. }
  257. public static void main(String[] args) {
  258. //insert();
  259. //update();
  260. //query();
  261. delete();
  262. }
  263. }