JDBC数据库应用开发

JDBC技术原理

JDBC是Sun公司提出的Java API中的一部分，其含义是用Java语言来访问数据库，是java程序访问数据库的标准接口。在使用JDBC访问不同的数据库时，需要加载数据库的驱动程序，这些驱动程序由数据库厂商提供。下面给出JDBC连接各种主流数据库的连接方式：

1、连接Oracle 8/8i/9i/10g/11g（thin模式）

Class.forName("oracle.JDBC.driver.OracleDriver").newInstance();String url="JDBC:oracle:thin:@localhost:1521:orcl"       //orcl为Oracle数据库的SIDString user="test";String password="test";Connection con=DriverManager.getConnection(url,user,password);

2、连接DB2数据库

Class.forName("com.ibm.db2.jcc.DB2Driver");String url="JDBC:db2://localhost:5000/testDb";String user="test"; String password="test";Connection con=DriverManager.getConnection(url,user,password);

3、连接MySQL数据库

Class.forName("com.mysql.jdbc.Driver");String url="JDBC:mysql://localhost:8080/testDB";String user="test"; String password="test";Connection con=DriverManager.getConnection(url,user,password);

4、连接SQL Server2000数据库

Class.forName("com.microsoft.JDBC.sqlserver.SQLServerDriver");String url="JDBC:microsoft:sqlserver://localhost:1433;DatabaseName=testDb";String user="test"; String password="test";Connection con=DriverManager.getConnection(url,user,password);

5、连接PostgreSQL数据库

Class.forName("org.postgresql.Driver");String url="JDBC:postgresql://localhost/testDb";String user="test"; String password="test";Connection con=DriverManager.getConnection(url,user,password);

6、连接Access数据库

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");String url="JDBC:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+application.getRealPath("/Data/testDb/mdb");Connection conn=DriverManager.getConnection(url,"","");

7、连接Sybase数据库

Class.forName("com.sybase.JDBC.SybDriver");String url="JDBC:sybase:Tds:localhost:5007/testDb";Properties pro=System.getProperties();pro.put("user","userId");pro.put("password","user_password");Connection con=DriverManager.getConnection(url,pro);

8、连接informix数据库

Class.forName("com.informix.JDBC.ifxDriver");String url="JDBC:informix-sqli:localhost:1533/testDb:INFORMIXSERVER=myserver"user=testUser;password=testpassword"; Connection con=DriverManager.getConnection(url);

JDBC连接数据库的过程很简单，以连接MySQL数据库为例，具体步骤如下：

（1）加载MySQL数据库连接的驱动程序。可以到MySQL的官网上下载该驱动程序jar包，然后把jar包放到工程项目的WEB-INF/lib目录下。加载数据库驱动程序使用的是Class.forName()方法，调用此方法会将制定的类加载到JVM中，其关键代码如下：

Class.forName("com.mysql.jdbc.Driver")

(2) 设置访问数据库的用户名、密码及连接URL。不同的数据库其连接URL有所不同，但基本格式都是“JDBC协议+数据库的IP地址+数据库端口号+数据库名”。关键代码如下：

String user = "root";String password = "root";String url = "jdbc:mysql://localhost:3306/database";

(3)通过JDBC API的DriverManager类的getConnection()方法来创建与数据库之间的连接，getConnection()方法需要接受的参数有URL、用户名和密码，关键代码如下：

Connection conn = DriverManager.getConnction(url,user,password);

(4)建立连接之后，使用该连接对象创建用户操作SQL语句的PreparedStatement对象，使用的是prepareStatement()方法，关键代码如下：

PreparedStatement ps = conn.prepareStatement(sql);

(5)关闭数据库连接，释放系统资源，代码如下：

conn.close();

下面给出一个JDBC连接MySQL数据库的完整代码示例，这个示例采用了MVC设计模式，单例模式。JSP作为表现层，Servlet是业务控制层，Dao是操作实体类的持久层，详细代码如下：

User.java ---- 用户实体类

package com.kj.test;public class User {private Long id;/** 姓名 */private String name;/** 性别 */private String sex;/** 年龄 */private String age;public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getAge() {return age;}public void setAge(String age) {this.age = age;}}

DBConn.java ---- MySQL数据库连接类

package com.kj.test;import java.sql.Connection;import java.sql.DriverManager;public class DBConn {/** 加载连接 */private static Connection conn = null;/** MySQL数据库驱动名 */private static final String DRIVERNAME = "com.mysql.jdbc.Driver";/** 用户 */private static final String USERNAME = "root";/** 密码 */private static final String PASSWORD = "root";/** 连接URL */private static final String URL = "jdbc:mysql://localhost:3306/test";public static Connection getConn() { try {Class.forName(DRIVERNAME);conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);} catch (Exception e) {e.printStackTrace();}return conn;}public static void main(String[] args) {//测试是否已经连接成功Connection conn = DBConn.getConn();System.out.println("conn:"+conn);}}

UserDao.java ---- 持久化User模型到数据库

package com.kj.test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;public class UserDao {private static UserDao userDao = null;/*** * 单例模式获取userDao * @return */public static UserDao getInstance() {if (userDao == null) {userDao = new UserDao();}return userDao;}/** * 保存一个user对象到数据库 * @param user * @return */public boolean saveUser(User user) {boolean result = false;Connection conn = null;PreparedStatement ps = null;try {conn = DBConn.getConn();String sql = "insert into user(name,sex,age) values(?,?,?)";ps = conn.prepareStatement(sql);ps.setString(1, user.getName());ps.setString(2, user.getSex());ps.setString(3, user.getAge());int n = ps.executeUpdate();if (n == 1) {result = true; //保存成功}} catch (Exception e) {e.printStackTrace();} finally {try {ps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}return result;}/** * 从数据库中加载表数据放入对象列表 * @return */public List<User> getUserList() {List<User> userList = new ArrayList<User>();Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = DBConn.getConn();String sql = "select * from user";ps = conn.prepareStatement(sql);rs = ps.executeQuery();while (rs.next()) {User user = new User();user.setId(rs.getLong("id"));user.setName(rs.getString("name"));user.setSex(rs.getString("sex"));user.setAge(rs.getString("age"));userList.add(user);}} catch (Exception e) {e.printStackTrace();} finally {try {rs.close();ps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}return userList;}/** * 修改user对象信息并持久化到数据库 * @param args */public boolean updateUser(User user) {boolean result = false;Connection conn = null;PreparedStatement ps = null;try {conn = DBConn.getConn();String sql = "update user set age = ?, sex = ? where name = ?";ps = conn.prepareStatement(sql);ps.setString(1, user.getAge());ps.setString(2, user.getSex());ps.setString(3, user.getName());int n = ps.executeUpdate();if (n == 1) {result = true; //更新成功}} catch (Exception e) {e.printStackTrace();} finally {try {ps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}return result;}/** * 删除一条user对象对应的数据库记录 * @param args */public boolean deleteUser(User user) {boolean result = false;Connection conn = null;PreparedStatement ps = null;try {conn = DBConn.getConn();String sql = "delete from user where name = ?";ps = conn.prepareStatement(sql);ps.setString(1, user.getName());int n = ps.executeUpdate();if (n == 1) {result = true; //删除成功}} catch (Exception e) {e.printStackTrace();} finally {try {ps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}return result;}public static void main(String[] args) {UserDao ud = UserDao.getInstance();User user = new User();user.setAge("1岁");user.setSex("男");user.setName("测试");ud.saveUser(user);ud.deleteUser(user);}}

MyFilter.java ---- 过滤JSP页面传递过来的中文字符

package com.kj.test;import java.io.IOException;import javax.servlet.Filter;import javax.servlet.FilterChain;import javax.servlet.FilterConfig;import javax.servlet.ServletException;import javax.servlet.ServletRequest;import javax.servlet.ServletResponse;public class MyFilter implements Filter {private String encode;public MyFilter() {    }public void destroy() {}public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {if (encode != null) {request.setCharacterEncoding(encode);response.setContentType("text/html;charset="+encode);}chain.doFilter(request, response);}public void init(FilterConfig fConfig) throws ServletException {encode = fConfig.getInitParameter("encode");}}

AddUserServlet.java ---- 添加用户Servlet

package com.kj.test;import java.io.IOException;import javax.servlet.RequestDispatcher;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;public class AddUserServlet extends HttpServlet {private static final long serialVersionUID = 1L;           public AddUserServlet() {        super();    }protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {String name = request.getParameter("name");String sex = request.getParameter("sex");String age = request.getParameter("age");User user = new User();user.setAge(age);user.setName(name);user.setSex(sex);UserDao userDao = UserDao.getInstance();boolean result = userDao.saveUser(user); String url;if (result) {url = "/showUser";} else {url = "error.jsp";}RequestDispatcher dispathcer = request.getRequestDispatcher(url);dispathcer.forward(request, response);}}

ShowUserServlet.java ---- 显示用户列表Servlet

package com.kj.test;import java.io.IOException;import java.util.List;import javax.servlet.RequestDispatcher;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;public class ShowUserServlet extends HttpServlet {private static final long serialVersionUID = 1L;           public ShowUserServlet() {        super();    }protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {UserDao userDao = UserDao.getInstance();List<User> userList = userDao.getUserList();request.setAttribute("users", userList);String url = "manageUser.jsp";RequestDispatcher dispathcer = request.getRequestDispatcher(url);dispathcer.forward(request, response);}}

web.xml ---- 配置Servlet和过滤器Filter

<?xml version="1.0" encoding="UTF-8"?><web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">  <display-name>servletTest</display-name>  <!-- servlet配置示例 -->  <servlet>  <!-- 增加用户Servlet -->    <servlet-name>AddUserServlet</servlet-name>    <servlet-class>com.kj.test.AddUserServlet</servlet-class>  </servlet>  <servlet-mapping>    <servlet-name>AddUserServlet</servlet-name>    <url-pattern>/addUser</url-pattern>  </servlet-mapping>  <servlet>  <!-- 显示用户Servlet -->    <servlet-name>ShowUserServlet</servlet-name>    <servlet-class>com.kj.test.ShowUserServlet</servlet-class>  </servlet>  <servlet-mapping>    <servlet-name>ShowUserServlet</servlet-name>    <url-pattern>/showUser</url-pattern>  </servlet-mapping>  <!-- filter配置示例 -->  <filter>  <filter-name>MyFilter</filter-name>  <filter-class>com.kj.test.MyFilter</filter-class>  <init-param>  <param-name>encode</param-name>  <param-value>UTF-8</param-value>  </init-param>  </filter>  <filter-mapping>  <filter-name>MyFilter</filter-name>  <url-pattern>/*</url-pattern>  </filter-mapping>  <welcome-file-list>    <welcome-file>index.html</welcome-file>    <welcome-file>index.htm</welcome-file>    <welcome-file>index.jsp</welcome-file>    <welcome-file>default.html</welcome-file>    <welcome-file>default.htm</welcome-file>    <welcome-file>default.jsp</welcome-file>  </welcome-file-list></web-app>

index.jsp ---- 添加用户入口页面

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>添加用户入口</title></head><body><form action="addUser" method="post"><div style="margin-top:100px;text-align:center;margin-bottom:10px;font-size:24px;font-weight:bolder">添加用户页面</div><table align="center" cellpadding="0" cellspacing="0" ><tr><td style="text-align:right">姓名：</td><td style="text-align:left"><input type="text" name="name" value="" /></td></tr><tr><td style="text-align:right">性别：</td><td style="text-align:left"><input type="radio" name="sex" value="男" />男 <input type="radio" name="sex" value="女" />女</td></tr><tr><td style="text-align:right">年龄：</td><td style="text-align:left"><input type="text" name="age" value="" /></td></tr></table><div style="margin-top:10px;text-align:center;"><input type="submit" value="增加"/></div></form></body></html>

 manageUser.jsp ---- 用户管理列表页面

<%@page import="com.kj.test.User"%><%@page import="java.util.List"%><%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%><%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>管理用户页面</title></head><body><div style="margin-top:100px;text-align:center;margin-bottom:10px;font-size:24px;font-weight:bolder">管理用户页面</div><table align="center" border="1" cellpadding="0" cellspacing="0" ><c:set var="userList" value="${requestScope.users }" /><c:if test="${not empty userList }"><tr><th style="text-align:center;width:100px;">主键ID</th><th style="text-align:center;width:100px;">姓名</th><th style="text-align:center;width:100px;">性别</th><th style="text-align:center;width:100px;">年龄</th></tr><c:forEach items="${userList }" var="user"><tr><td style="text-align:center;width:100px;"><c:out value="${user.id }"/></td><td style="text-align:center;width:100px;"><c:out value="${user.name }"/></td><td style="text-align:center;width:100px;"><c:out value="${user.sex }"/></td><td style="text-align:center;width:100px;"><c:out value="${user.age }"/></td></tr></c:forEach></c:if><c:if test="${empty userList }"><div style="margin-top:20px;text-align:center;margin-bottom:10px;font-size:18px;font-weight:bolder;color:red">对不起，当前还没有用户记录，请先返回添加页面进行添加。</div></c:if></table><div style="margin-top:20px;text-align:center;"><a href="index.jsp">返回添加页面</a></div></body></html>

error.jsp ---- 如果出错则跳到此提示页面

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>错误页面</title></head><body><div style="color:red;text-align:center;margin-top:100px;font-size: 20px;">操作失败，<a href="index.jsp">返回用户添加页面</a>或者<a href="manageUser.jsp">返回用户管理页面</a></div></body></html>