JDBC数据库应用开发

最新推荐文章于 2022-12-10 19:57:08 发布

轩辕无痕

最新推荐文章于 2022-12-10 19:57:08 发布

阅读量482

点赞数 2

分类专栏： JAVA Web编程宝典文章标签： JDBC技术

本文链接：https://blog.csdn.net/kookkj/article/details/51203595

版权

JAVA Web编程宝典专栏收录该内容

26 篇文章 0 订阅

订阅专栏

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数据库的SID
String 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>