1.jdbc访问
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBHelper {
<span style="white-space:pre"> </span> private static final String DBDRIVER = "com.mysql.jdbc.Driver";
<span style="white-space:pre"> </span> private static final String DBURL = "jdbc:mysql://127.0.0.1:3306/JspBookShop";
<span style="white-space:pre"> </span> private static final String DBUSER = "root";
<span style="white-space:pre"> </span> private static final String DBPASS = "";
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span> private static Connection conn=null;
<span style="white-space:pre"> </span> private static Statement stmt=null;
<span style="white-space:pre"> </span> private static ResultSet rs=null;
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>public static boolean open(){
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>Context ic = new InitialContext();
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>DataSource source =
<span style="white-space:pre"> </span>(DataSource)ic.lookup("java:comp/env/JspBookShop");
<span style="white-space:pre"> </span> conn = source.getConnection();
<span style="white-space:pre"> </span>System.out.println("建立连接成功");
<span style="white-space:pre"> </span>stmt=conn.createStatement();
<span style="white-space:pre"> </span>} catch (SQLException e) {
<span style="white-space:pre"> </span>System.out.println("建立连接失败");
<span style="white-space:pre"> </span>return false;
<span style="white-space:pre"> </span>} catch (NamingException e) {
<span style="white-space:pre"> </span>e.printStackTrace();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>return true;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>public static void close(){
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>if(rs!=null){
<span style="white-space:pre"> </span>rs.close();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>if(stmt!=null)
<span style="white-space:pre"> </span>stmt.close();
<span style="white-space:pre"> </span>if(conn!=null)
<span style="white-space:pre"> </span>conn.close();
<span style="white-space:pre"> </span>} catch (SQLException e) {
<span style="white-space:pre"> </span>System.out.println("数据库关闭异常");
<span style="white-space:pre"> </span>e.printStackTrace();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>public static int executeUpadate(String sql){
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>open();
<span style="white-space:pre"> </span>return stmt.executeUpdate(sql);
<span style="white-space:pre"> </span>} catch (SQLException e) {
<span style="white-space:pre"> </span>System.out.println("未能获取到数据连接");
<span style="white-space:pre"> </span>return -1;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static ResultSet executeQuery(String sql){
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>open();
<span style="white-space:pre"> </span>return stmt.executeQuery(sql);
<span style="white-space:pre"> </span>} catch (SQLException e) {
<span style="white-space:pre"> </span>System.out.println("sql语句不正确");
<span style="white-space:pre"> </span>return null;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
}
传统数据库连接方式的不足
每一次请求时均需要与数据库进行连接,资源占用较多
当并发访问数量较大时,网站速度收到极大影响
在访问结束后必须要关闭连接释放资源
系统的安全性和稳定性相对较差
JDDI访问的效率会比jdbc高,具体做法是现在tomcat中conf文件夹内的context文件加入一段配置文件,就像以下这样
<Resource name="JspBookShop"
auth="Container" type="javax.sql.DataSource" maxActive="100"
maxIdle="30" maxWait="10000" username="root" password=""
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1:3306/JspBookShop"/>
每个字段的解释如下
JNDI把获取数据库的驱动,url,账号密码全部写在xml里,通过反射读取xml文本内的数据,其他的和普通jdbc没有区别,所以只需要改变open方法内的几行代码即可,这里需要注意的是数据库的驱动jar包放在tomcat的lib文件夹中。还有一点是此连接池是在tomcat中,运行时需要启动tomcat
public static boolean open(){
try {
Context ic = new InitialContext();
DataSource source =
(DataSource)ic.lookup("java:comp/env/JspBookShop");
conn = source.getConnection();
System.out.println("建立连接成功");
stmt=conn.createStatement();
} catch (SQLException e) {
System.out.println("建立连接失败");
return false;
} catch (NamingException e) {
e.printStackTrace();
}
return true;
}