数据库直连 ---- JDBC:
加载驱动DriverManger-----获得Connection对象-----创建一个Statement----使用结果集(ResultSet)对象
数据连接池 ---- JNDI:
开发步骤:
1:配置数据源
在Tomcat下配置路径 tomcat---conf---context.xml
配置内容:Ip 端口号 实例名 数据库用户名 密码
功能代码:
<Resource
name="jdbc/abc" //数据源名称,一般以jdbc为前缀
type="javax.sql.DataSource"
username="Pioneer.HengYu"
password="Pioneer.HengYu"
url="jdbc:oracle:thin:@localhost:1521:XE"
driverClassName="oracle.jdbc.driver.OracleDriver"
maxIdle="2000" //最大连接数
maxWait="5000" //最大等待时长
maxActive="400"/> //最大活跃数
2:配置web应用和连接池的映射 在项目web.xml中配置
1 web.xml中配置映射 2 3 <?xml version="1.0" encoding="UTF-8"?> 4 <web-app version="2.5" 5 xmlns="http://java.sun.com/xml/ns/javaee" 6 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 7 xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 8 http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> 9 <welcome-file-list> 10 <welcome-file>index.jsp</welcome-file> 11 </welcome-file-list> 12 13 <!--连接池的映射 --> 14 <resource-ref> 15 <description>JNDI</description> 16 <res-ref-name>jdbc/abc</res-ref-name> 17 <res-type>javax.sql.DataSource</res-type> 18 <res-auth>Container</res-auth> 19 </resource-ref> 20 21 22 </web-app>
•需求:查询所有客户信息
数据库建表,起名 T_client
路径
创建实体类
1 实体类Client 2 3 package client; 4 5 public class Client { //客户对象 ---对应客户表 对象的属性---表中的字段 一行记录---一个对象 6 7 String clientNo; 8 String clientName; 9 String sex; 10 String birthday; 11 String certificateNO; 12 String telNO; 13 String clientType; 14 String cardNo; 15 16 public String getClientNo() { 17 return clientNo; 18 } 19 public void setClientNo(String clientNo) { 20 this.clientNo = clientNo; 21 } 22 public String getClientName() { 23 return clientName; 24 } 25 public void setClientName(String clientName) { 26 this.clientName = clientName; 27 } 28 public String getSex() { 29 return sex; 30 } 31 public void setSex(String sex) { 32 this.sex = sex; 33 } 34 public String getBirthday() { 35 return birthday; 36 } 37 public void setBirthday(String birthday) { 38 this.birthday = birthday; 39 } 40 public String getCertificateNO() { 41 return certificateNO; 42 } 43 public void setCertificateNO(String certificateNO) { 44 this.certificateNO = certificateNO; 45 } 46 public String getTelNO() { 47 return telNO; 48 } 49 public void setTelNO(String telNO) { 50 this.telNO = telNO; 51 } 52 public String getClientType() { 53 return clientType; 54 } 55 public void setClientType(String clientType) { 56 this.clientType = clientType; 57 } 58 public String getCardNo() { 59 return cardNo; 60 } 61 public void setCardNo(String cardNo) { 62 this.cardNo = cardNo; 63 } 64 public Client() { 65 66 } 67 public Client(String clientNo, String clientName, String sex, 68 String birthday, String certificateNO, String telNO, 69 String clientType, String cardNo) { 70 71 this.clientNo = clientNo; 72 this.clientName = clientName; 73 this.sex = sex; 74 this.birthday = birthday; 75 this.certificateNO = certificateNO; 76 this.telNO = telNO; 77 this.clientType = clientType; 78 this.cardNo = cardNo; 79 } 80 81 }
通过JNDI连接数据库
1 ClientJNDI类 2 3 package client; 4 5 import java.sql.Connection; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import java.sql.Statement; 9 import java.util.ArrayList; 10 import java.util.List; 11 12 import javax.naming.Context; 13 import javax.naming.InitialContext; 14 import javax.naming.NamingException; 15 import javax.sql.DataSource; 16 17 public class ClientJNDI { 18 19 // 查询 20 public List getClient() { 21 22 // 创建集合 23 List<Client> clientList = new ArrayList<Client>(); 24 25 // 通过数据库连接池连接数据库,查询 26 Context c1; 27 DataSource ds=null; 28 Connection con=null; 29 Statement sta=null; 30 ResultSet rs=null; 31 try { 32 c1 = new InitialContext();// 实例化上下文空间对象 33 ds=(DataSource)c1.lookup("java:comp/env/jdbc/abc"); //通过名字找到空闲的数据源 34 con=ds.getConnection(); //通过数据源得到连接对象 35 System.out.println("数据库连接池已连接"); 36 37 sta=con.createStatement(); 38 String sql="select * from t_client order by client_no desc"; //默认升序,倒序加desc 39 rs=sta.executeQuery(sql); 40 41 //将rs中的数据取出存放到list里 42 while(rs.next()){ 43 44 String clientNo=rs.getString("client_no"); 45 String clientName=rs.getString("client_name"); 46 String sex=rs.getString("sex"); 47 String birthday=rs.getString("birthday"); 48 String certificateNO=rs.getString("CERTIFICATE_NO"); 49 String telNO=rs.getString("tel_no"); 50 String clientType=rs.getString("client_type"); 51 String cardNo=rs.getString("card_no"); 52 53 //封装客户对象 54 Client client=new Client(clientNo, clientName, sex, birthday, certificateNO, telNO, clientType, cardNo); 55 56 //将客户对象存入集合中 57 clientList.add(client); 58 } 59 60 } catch (NamingException e) { 61 // TODO Auto-generated catch block 62 e.printStackTrace(); 63 } catch (SQLException e) { 64 // TODO Auto-generated catch block 65 e.printStackTrace(); 66 }finally{ 67 68 try { 69 if(rs!=null){ 70 rs.close(); 71 } 72 } catch (SQLException e) { 73 // TODO Auto-generated catch block 74 e.printStackTrace(); 75 } 76 77 try { 78 if(con!=null &&! con.isClosed()){ 79 con.close(); 80 } 81 } catch (SQLException e) { 82 // TODO Auto-generated catch block 83 e.printStackTrace(); 84 } 85 86 } 87 88 return clientList; 89 } 90 91 }
页面上取值
1 查询到数据显示的页面client.jsp 2 3 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> 4 <%@page import="client.ClientJNDI"%> 5 <%@page import="client.Client"%> 6 <% 7 String path = request.getContextPath(); 8 String basePath = request.getScheme() + "://" 9 + request.getServerName() + ":" + request.getServerPort() 10 + path + "/"; 11 %> 12 13 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 14 <html> 15 <head> 16 <base href="<%=basePath%>"> 17 18 <title>My JSP 'client.jsp' starting page</title> 19 20 <meta http-equiv="pragma" content="no-cache"> 21 <meta http-equiv="cache-control" content="no-cache"> 22 <meta http-equiv="expires" content="0"> 23 <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> 24 <meta http-equiv="description" content="This is my page"> 25 <!-- 26 <link rel="stylesheet" type="text/css" href="styles.css"> 27 --> 28 29 </head> 30 31 <body> 32 33 <!-- 调用JNDI获得数据, 显示客户信息 --> 34 35 <table border="1" width="1000"> 36 <tr> 37 <td> 38 客户编号 39 </td> 40 <td> 41 客户姓名 42 </td> 43 <td> 44 客户性别 45 </td> 46 <td> 47 客户生日 48 </td> 49 <td> 50 客户身份证号 51 </td> 52 <td> 53 客户电话号 54 </td> 55 <td> 56 客户类型 57 </td> 58 <td> 59 卡号 60 </td> 61 </tr> 62 <% 63 ClientJNDI clientJNDI = new ClientJNDI(); 64 List clientList = clientJNDI.getClient(); 65 66 //循环List,并显示数据 67 for (int i = 0; i < clientList.size(); i++) { 68 Client c = (Client) clientList.get(i); 69 %> 70 <tr> 71 <td><%=c.getClientNo()%></td> 72 <td><%=c.getClientName()%></td> 73 <td><%=c.getSex()%></td> 74 <td><%=c.getBirthday()%></td> 75 <td><%=c.getCertificateNO()%></td> 76 <td><%=c.getTelNO()%></td> 77 <td><%=c.getClientType()%></td> 78 <td><%=c.getCardNo()%></td> 79 </tr> 80 81 <% 82 } 83 %> 84 85 </table> 86 87 88 </body> 89 </html>
输入 http://localhost:8088/myJNDI/client.jsp 路径 测试结果