JDBC数据库应用开发

JDBC数据库应用开发JDBC技术原理JDBC是Sun公司提出的Java API中的一部分，其含义是用Java语言来访问数据库，是java程序访问数据库的标准接口。

在使用JDBC访问不同的数据库时，需要加载数据库的驱动程序，这些驱动程序由数据库厂商提供。

下面给出JDBC连接各种主流数据库的连接方式：1、连接Oracle 8/8i/9i/10g/11g（thin模式）[html]view plain copy1.Class.forName("oracle.JDBC.driver.OracleDriver").newInstance();2.String url="JDBC:oracle:thin:@localhost:1521:orcl" //orcl为Oracle数据库的SID3.String user="test";4.String password="test";5.Connection con=DriverManager.getConnection(url,user,password);2、连接DB2数据库[html]view plain copy1.Class.forName("com.ibm.db2.jcc.DB2Driver");2.String url="JDBC:db2://localhost:5000/testDb";3.String user="test"; String password="test";4.Connection con=DriverManager.getConnection(url,user,password);3、连接MySQL数据库[html]view plain copy1.Class.forName("com.mysql.jdbc.Driver");2.String url="JDBC:mysql://localhost:8080/testDB";3.String user="test"; String password="test";4.Connection con=DriverManager.getConnection(url,user,password);4、连接SQL Server2000数据库[html]view plain copy1.Class.forName("com.microsoft.JDBC.sqlserver.SQLServerDriver");2.String url="JDBC:microsoft:sqlserver://localhost:1433;DatabaseName=testDb";3.String user="test"; String password="test";4.Connection con=DriverManager.getConnection(url,user,password);5、连接PostgreSQL数据库[html]view plain copy1.Class.forName("org.postgresql.Driver");2.String url="JDBC:postgresql://localhost/testDb";3.String user="test"; String password="test";4.Connection con=DriverManager.getConnection(url,user,password);6、连接Access数据库[html]view plain copy1.Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");2.String url="JDBC:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+application.getRealPath("/Data/testDb/mdb");3.Connection conn=DriverManager.getConnection(url,"","");7、连接Sybase数据库[html]view plain copy1.Class.forName("com.sybase.JDBC.SybDriver");2.String url="JDBC:sybase:Tds:localhost:5007/testDb";3.Properties pro=System.getProperties();4.pro.put("user","userId");5.pro.put("password","user_password");6.Connection con=DriverManager.getConnection(url,pro);8、连接informix数据库[html]view plain copy1.Class.forName("rmix.JDBC.ifxDriver");2.String url="JDBC:informix-sqli:localhost:1533/testDb:INFORMIXSERVER=myserver"user=testUser;password=testpassword"; Connection con=DriverManager.getConne ction(url);JDBC连接数据库的过程很简单，以连接MySQL数据库为例，具体步骤如下：（1）加载MySQL数据库连接的驱动程序。

可以到MySQL的官网上下载该驱动程序jar 包，然后把jar包放到工程项目的WEB-INF/lib目录下。

加载数据库驱动程序使用的是Class.forName()方法，调用此方法会将制定的类加载到JVM中，其关键代码如下：[html]view plain copy1.Class.forName("com.mysql.jdbc.Driver")(2) 设置访问数据库的用户名、密码及连接URL。

不同的数据库其连接URL有所不同，但基本格式都是“JDBC协议+数据库的IP地址+数据库端口号+数据库名”。

关键代码如下：[html]view plain copy1.String user = "root";2.String password = "root";3.String url = "jdbc:mysql://localhost:3306/database";(3)通过JDBC API的DriverManager类的getConnection()方法来创建与数据库之间的连接，getConnection()方法需要接受的参数有URL、用户名和密码，关键代码如下：[html]view plain copy1.Connection conn = DriverManager.getConnction(url,user,password);(4)建立连接之后，使用该连接对象创建用户操作SQL语句的PreparedStatement对象，使用的是prepareStatement()方法，关键代码如下：[html]view plain copy1.PreparedStatement ps = conn.prepareStatement(sql);(5)关闭数据库连接，释放系统资源，代码如下：[html]view plain copy1.conn.close();下面给出一个JDBC连接MySQL数据库的完整代码示例，这个示例采用了MVC设计模式，单例模式。

JSP作为表现层，Servlet是业务控制层，Dao是操作实体类的持久层，详细代码如下：User.java ---- 用户实体类[html]view plain copy1.package com.kj.test;2.3.public class User {4.5. private Long id;6. /** 姓名 */7. private String name;8. /** 性别 */9. private String sex;10. /** 年龄 */11. private String age;12.13.14. public Long getId() {15. return id;16. }17.18. public void setId(Long id) {19.this.id = id;20. }21.22. public String getName() {23. return name;24. }25.26. public void setName(String name) { = name;28. }29.30. public String getSex() {31. return sex;32. }33.34. public void setSex(String sex) {35.this.sex = sex;36. }37.38. public String getAge() {39. return age;40. }41.42. public void setAge(String age) {43.this.age = age;44. }45.46.}DBConn.java ---- MySQL数据库连接类[html]view plain copy1.package com.kj.test;2.3.import java.sql.Connection;4.import java.sql.DriverManager;5.6.public class DBConn {7.8. /** 加载连接 */9. private static Connection conn = null;10. /** MySQL数据库驱动名 */11. private static final String DRIVERNAME = "com.mysql.jdbc.Driver";12. /** 用户 */13. private static final String USERNAME = "root";14. /** 密码 */15. private static final String PASSWORD = "root";16. /** 连接URL */17. private static final String URL = "jdbc:mysql://localhost:3306/test";18.19. public static Connection getConn() {20. try {21. Class.forName(DRIVERNAME);22.conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);23. } catch (Exception e) {24. e.printStackTrace();25. }26. return conn;27. }28.29. public static void main(String[] args) {30.31. //测试是否已经连接成功32. Connection conn = DBConn.getConn();33. System.out.println("conn:"+conn);34.35. }36.37.}UserDao.java ---- 持久化User模型到数据库[html]view plain copy1.package com.kj.test;2.3.import java.sql.Connection;4.import java.sql.PreparedStatement;5.import java.sql.ResultSet;6.import java.util.ArrayList;7.import java.util.List;8.9.public class UserDao {10.11. private static UserDao userDao = null;12.13. /***14. * 单例模式获取userDao15. * @return16. */17. public static UserDao getInstance() {18. if (userDao == null) {erDao = new UserDao();20. }21. return userDao;22. }23.24. /**25. * 保存一个user对象到数据库26. * @param user27. * @return28. */29. public boolean saveUser(User user) {30. boolean result = false;31. Connection conn = null;32. PreparedStatement ps = null;33. try {34.conn = DBConn.getConn();35. String sql = "insert into user(name,sex,age) values(?,?,?)";36.ps = conn.prepareStatement(sql);37. ps.setString(1, user.getName());38. ps.setString(2, user.getSex());39. ps.setString(3, user.getAge());40. int n = ps.executeUpdate();41. if (n == 1) {42.result = true; //保存成功43. }44. } catch (Exception e) {45. e.printStackTrace();46. } finally {47. try {48. ps.close();49. conn.close();50. } catch (Exception e) {51. e.printStackTrace();52. }53. }54. return result;55. }56.57. /**58. * 从数据库中加载表数据放入对象列表59. * @return60. */61. public List<User> getUserList() {62. List<User>userList = new ArrayList<User>();63. Connection conn = null;64. PreparedStatement ps = null;65. ResultSet rs = null;66. try {67.conn = DBConn.getConn();68. String sql = "select * from user";69.ps = conn.prepareStatement(sql);70.rs = ps.executeQuery();71. while (rs.next()) {72. User user = new User();73. user.setId(rs.getLong("id"));74. user.setName(rs.getString("name"));75. user.setSex(rs.getString("sex"));76. user.setAge(rs.getString("age"));77. userList.add(user);78. }79. } catch (Exception e) {80. e.printStackTrace();81. } finally {82. try {83. rs.close();84. ps.close();85. conn.close();86. } catch (Exception e) {87. e.printStackTrace();88. }89. }90.91. return userList;92. }93.94. /**95. * 修改user对象信息并持久化到数据库96. * @param args97. */98. public boolean updateUser(User user) {99. boolean result = false;100. Connection conn = null;101. PreparedStatement ps = null;102. try {103.conn = DBConn.getConn();104. String sql = "update user set age = ?, sex = ? where name = ?"; 105.ps = conn.prepareStatement(sql);106. ps.setString(1, user.getAge());107. ps.setString(2, user.getSex());108. ps.setString(3, user.getName());109. int n = ps.executeUpdate();110. if (n == 1) {111.result = true; //更新成功112. }113. } catch (Exception e) {114. e.printStackTrace();115. } finally {116. try {117. ps.close();118. conn.close();119. } catch (Exception e) {120. e.printStackTrace();121. }122. }123. return result;124. }125.126. /**127. * 删除一条user对象对应的数据库记录128. * @param args129. */130. public boolean deleteUser(User user) {131. boolean result = false;132. Connection conn = null;133. PreparedStatement ps = null;134. try {135.conn = DBConn.getConn();136. String sql = "delete from user where name = ?"; 137.ps = conn.prepareStatement(sql);138. ps.setString(1, user.getName());139. int n = ps.executeUpdate();140. if (n == 1) {141.result = true; //删除成功142. }143. } catch (Exception e) {144. e.printStackTrace();145. } finally {146. try {147. ps.close();148. conn.close();149. } catch (Exception e) {150. e.printStackTrace();151. }152. }153. return result;154. }155.156. public static void main(String[] args) {157. UserDao ud = UserDao.getInstance();158. User user = new User();159. user.setAge("1岁");160. user.setSex("男");161. user.setName("测试");162. ud.saveUser(user);163. ud.deleteUser(user);164. }165.166.}MyFilter.java ---- 过滤JSP页面传递过来的中文字符[html]view plain copy1.package com.kj.test;2.3.import java.io.IOException;4.import javax.servlet.Filter;5.import javax.servlet.FilterChain;6.import javax.servlet.FilterConfig;7.import javax.servlet.ServletException;8.import javax.servlet.ServletRequest;9.import javax.servlet.ServletResponse;10.11.public class MyFilter implements Filter {12.13. private String encode;14.15. public MyFilter() {16.17. }18.19. public void destroy() {20.21. }22.23. public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {24.25. if (encode != null) {26. request.setCharacterEncoding(encode);27. response.setContentType("text/html;charset="+encode);28. }29. chain.doFilter(request, response);30.31. }32.33. public void init(FilterConfig fConfig) throws ServletException {34.encode = fConfig.getInitParameter("encode");35. }36.37.}AddUserServlet.java ---- 添加用户Servlet[html]view plain copy1.package com.kj.test;2.import java.io.IOException;3.import javax.servlet.RequestDispatcher;4.import javax.servlet.ServletException;5.import javax.servlet.http.HttpServlet;6.import javax.servlet.http.HttpServletRequest;7.import javax.servlet.http.HttpServletResponse;8.9.public class AddUserServlet extends HttpServlet {10. private static final long serialVersionUID = 1L;11.12. public AddUserServlet() {13. super();14. }15.16. protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {17. String name = request.getParameter("name");18. String sex = request.getParameter("sex");19. String age = request.getParameter("age");20. User user = new User();21. user.setAge(age);22. user.setName(name);23. user.setSex(sex);24. UserDao userDao = UserDao.getInstance();25. boolean result = userDao.saveUser(user);26. String url;27. if (result) {28.url = "/showUser";29. } else {30.url = "error.jsp";31. }32. RequestDispatcher dispathcer = request.getRequestDispatcher(url);33. dispathcer.forward(request, response);34. }35.}ShowUserServlet.java ---- 显示用户列表Servlet[html]view plain copy1.package com.kj.test;2.import java.io.IOException;3.import java.util.List;4.5.import javax.servlet.RequestDispatcher;6.import javax.servlet.ServletException;7.import javax.servlet.http.HttpServlet;8.import javax.servlet.http.HttpServletRequest;9.import javax.servlet.http.HttpServletResponse;10.11.public class ShowUserServlet extends HttpServlet {12.13. private static final long serialVersionUID = 1L;14.15. public ShowUserServlet() {16. super();17. }18.19. protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {20.21. UserDao userDao = UserDao.getInstance();22. List<User>userList = userDao.getUserList();23. request.setAttribute("users", userList);24. String url = "manageUser.jsp";25. RequestDispatcher dispathcer = request.getRequestDispatcher(url);26. dispathcer.forward(request, response);27.28. }29.}web.xml ---- 配置Servlet和过滤器Filter[html]view plain copy1.<?xml version="1.0"encoding="UTF-8"?>2.<web-app xmlns:xsi="http://www.w/2001/XMLSchema-instance"xmlns="http:///xml/ns/javaee"3.xsi:schemaLocation="/xml/ns/javaee /xml/ns/javaee/web-app_3_0.xsd"id="WebApp_ID"version="3.0">4.<display-name>servletTest</display-name>5.<!-- servlet配置示例 -->6.<servlet>7.<!-- 增加用户Servlet -->8.<servlet-name>AddUserServlet</servlet-name>9.<servlet-class>com.kj.test.AddUserServlet</servlet-class>10.</servlet>11.<servlet-mapping>12.<servlet-name>AddUserServlet</servlet-name>13.<url-pattern>/addUser</url-pattern>14.</servlet-mapping>15.<servlet>16.<!-- 显示用户Servlet -->17.<servlet-name>ShowUserServlet</servlet-name>18.<servlet-class>com.kj.test.ShowUserServlet</servlet-class>19.</servlet>20.<servlet-mapping>21.<servlet-name>ShowUserServlet</servlet-name>22.<url-pattern>/showUser</url-pattern>23.</servlet-mapping>24.<!-- filter配置示例 -->25.<filter>26.<filter-name>MyFilter</filter-name>27.<filter-class>com.kj.test.MyFilter</filter-class>28.<init-param>29.<param-name>encode</param-name>30.<param-value>UTF-8</param-value>31.</init-param>32.</filter>33.<filter-mapping>34.<filter-name>MyFilter</filter-name>35.<url-pattern>/*</url-pattern>36.</filter-mapping>37.<welcome-file-list>38.<welcome-file>index.html</welcome-file>39.<welcome-file>index.htm</welcome-file>40.<welcome-file>index.jsp</welcome-file>41.<welcome-file>default.html</welcome-file>42.<welcome-file>default.htm</welcome-file>43.<welcome-file>default.jsp</welcome-file>44.</welcome-file-list>45.</web-app>index.jsp ---- 添加用户入口页面[html]view plain copy1.<%@ page language="java"contentType="text/html; charset=UTF-8"2.pageEncoding="UTF-8"%>3.<!DOCTYPE html PUBLIC "-//W3C//DTD HTML4.01 Transitional//EN" "http://www.w/TR/html4/loose.dtd">4.<html>5.<head>6.<meta http-equiv="Content-Type"content="text/html; charset=UTF-8">7.<title>添加用户入口</title>8.</head>9.<body>10.<form action="addUser"method="post">11.<div style="margin-top:100px;text-align:center;margin-bottom:10px;font-size:24px;font-weight:bolder">添加用户页面</div>12.<table align="center"cellpadding="0"cellspacing="0">13.<tr>14.<td style="text-align:right">姓名：</td>15.<td style="text-align:left"><input type="text"name="name"value=""/></td>16.</tr>17.<tr>18.<td style="text-align:right">性别：</td>19.<td style="text-align:left"><input type="radio"name="sex"value="男"/>男<input type="radio"name="sex"value="女"/>女</td>20.</tr>21.<tr>22.<td style="text-align:right">年龄：</td>23.<td style="text-align:left"><input type="text"name="age"value=""/></td>24.</tr>25.</table>26.<div style="margin-top:10px;text-align:center;"><input type="submit"value="增加"/></div>27.</form>28.</body>29.</html>manageUser.jsp ---- 用户管理列表页面[html]view plain copy1.<%@page import="er"%>2.<%@page import="java.util.List"%>3.<%@ page language="java"contentType="text/html; charset=UTF-8"4.pageEncoding="UTF-8"%>5.<%@ taglib prefix="c"uri="/jsp/jstl/core"%>6.<%@ taglib prefix="fmt"uri="/jsp/jstl/fmt"%>7.<%@ taglib prefix="fn"uri="/jsp/jstl/functions" %>8.<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w/TR/html4/loose.dtd">9.<html>10.<head>11.<meta http-equiv="Content-Type"content="text/html; charset=UTF-8">12.<title>管理用户页面</title>13.</head>14.<body>15.<div style="margin-top:100px;text-align:center;margin-bottom:10px;font-size:24px;font-weight:bolder">管理用户页面</div>16.<table align="center"border="1"cellpadding="0"cellspacing="0">17.<c:set var="userList"value="${ers }"/>18.<c:if test="${not empty userList }">19.<tr>20.<th style="text-align:center;width:100px;">主键ID</th>21.<th style="text-align:center;width:100px;">姓名</th>22.<th style="text-align:center;width:100px;">性别</th>23.<th style="text-align:center;width:100px;">年龄</th>24.</tr>25.<c:forEach items="${userList }"var="user">26.<tr>27.<td style="text-align:center;width:100px;"><c:out value="${user.id }"/></td>28.<td style="text-align:center;width:100px;"><c:out value="${ }"/></td>29.<td style="text-align:center;width:100px;"><c:out value="${user.sex }"/></td>30.<td style="text-align:center;width:100px;"><c:out value="${user.age }"/></td>31.</tr>32.</c:forEach>33.</c:if>34.<c:if test="${empty userList }">35.<div style="margin-top:20px;text-align:center;margin-bottom:10px;font-size:18px;font-weight:bolder;color:red">对不起，当前还没有用户记录，请先返回添加页面进行添加。