数据库连接的方式有很多种,常见的有jndi数据源、JDBC、配置文件jdbc等等.今天我们来说下使用jdbc来连接数据库:
首先,连接数据库所需要的四个参数(Driver、URL、UserName、PassWord),不同的数据库所需的参数就是这四个,只是内容不同罢了
第一种方式:
public void BaseDao{
protected Connection conn;
protected PreparedStatement ps;
protected Statement stmt;
protected ResultSet rs;
/**
* connect to Oracle
*
* @return
*/
public boolean getConnection() {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "scott";
String password = "tiger";
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
/**
* query from Oracle
*
* @param sql
* @param params
* @return
*/
public ResultSet query(String sql, Object[] params) {
getConnection();
try {
ps = conn.prepareStatement(sql);
if(null != params){
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
return rs;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 执行insert update delete语句
* @param insert update delete语句
* @param params 参数
* @return 是否执行成功
*/
public boolean saveResult(String sql,Object[] params){
boolean flag=false;
try {
this.getConnection();
//连接创建PreparedStatement对象
PreparedStatement smt = connection.prepareStatement(sql);
if(params!=null){
for (int i = 0; i <params.length; i++) {
smt.setObject(i+1, params[i]);
}
}
//向服务器发送SQL语句,返回受影响的行数
int row = smt.executeUpdate();
if(row==1){
flag=true;
}
} catch (Exception e) {
e.printStackTrace();
flag=false;
}finally{
this.closeResource();
}
return flag;
}
public void closeResource(){
try{
if(null !=rs){
rs.close();
}
if(null != ps){
ps.close();
}
if(null != stmt){
stmt.close();
}
if(null != conn){
conn.close();
}
}catch (Exception e) {
e.printStackTrace();
}
}}
<pre name="code" class="java">
这里我们使用的是Oracle数据库,调用数据库时,我们是需要把这个类继承过去即可
另外一种本人常用连接数据库的方式:jdni数据源连接,数据源的内容是写在Tomcat--conf--context.xml文件下的<Context> XXX </Context>之间
<Resource name="jdbc/webservice" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000" username="scott" password="tiger"
driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:orcl" />
其中,name = "jdbc/webservice" 这个webservice要与BaseDao中的
DataSource ds = (DataSource) cxt.lookup("java:comp/env/jdbc/webservice");
一致,不然系统会报错,找不到数据源名称之类的错误
第二种方式:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class BaseDao {
protected Connection connection;
private ResultSet rs;
protected Connection getConnection(){
try {
Context cxt = new InitialContext();
DataSource ds = (DataSource) cxt.lookup("java:comp/env/jdbc/scott");
//获取连接
connection= ds.getConnection();
} catch (Exception e) {
//e.printStackTrace();
}
return this.connection;
}
/**
* 执行select语句
* @param sql select语句
* @param params 参数的值
* @return
*/
public ResultSet getResult(String sql,Object[] params){
try {
this.getConnection();
//连接创建PreparedStatement对象
PreparedStatement smt = connection.prepareStatement(sql);
if(params!=null){
for (int i = 0; i <params.length; i++) {
smt.setObject(i+1, params[i]);
}
}
//向服务器发送SQL语句,并返回查询的结果集
this.rs = smt.executeQuery();
} catch (Exception e) {
//e.printStackTrace();
}
return rs;
}
/**
* 执行insert update delete语句
* @param insert update delete语句
* @param params 参数
* @return 是否执行成功
*/
public boolean saveResult(String sql,Object[] params){
boolean flag=false;
try {
this.getConnection();
//连接创建PreparedStatement对象
PreparedStatement smt = connection.prepareStatement(sql);
if(params!=null){
for (int i = 0; i <params.length; i++) {
smt.setObject(i+1, params[i]);
}
}
//向服务器发送SQL语句,返回受影响的行数
int row = smt.executeUpdate();
if(row==1){
flag=true;
}
} catch (Exception e) {
e.printStackTrace();
flag=false;
}finally{
this.closeResource();
}
return flag;
}
public void closeResource(){
try {
if (this.rs != null) {
rs.close();
}
if(this.connection!=null){
connection.close();
}
} catch (Exception e) {
// TODO: handle exception
}
}
}
这两种方式都是大差不差的,值得注意的是第二种方式中名称千万不要写错!
第三种方式使用配置文件连接数据库 (工厂模式)
db.properties(文件名)
driver_class=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
username=newsmgr
password=aptech
ConfigManager.java
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
* 读取配置文件的工具类
*
* @author lgladmin
*
*/
public class ConfigManager {
// 第一步:构建私有的静态的实例
private static ConfigManager configManager;
private static Properties properties;
// 第二步:构造函数私有化
private ConfigManager() {
String configFile = "db.properties";
properties = new Properties();
InputStream in = ConfigManager.class.getClassLoader()
.getResourceAsStream(configFile);
try {
properties.load(in);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 第三步:公开一个方法,返回单一实例
public static ConfigManager getInstance() {
if (configManager == null) {
configManager = new ConfigManager();
}
return configManager;
}
/**
* 根据key返回value
* @param key
* @return
*/
public String getString(String key) {
return properties.getProperty(key);
}
}
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;
public class Basedao {
protected Connection conn;
protected PreparedStatement ps;
protected Statement stmt;
protected ResultSet rs;
//获取数据库连接
public boolean getConnection(){
String driver = ConfigManager.getInstance().getString("jdbc.driverClassName");
String url = ConfigManager.getInstance().getString("jdbc.url");
String username = ConfigManager.getInstance().getString("jdbc.username");
String password = ConfigManager.getInstance().getString("jdbc.password");
try {
//(1)加载JDBC驱动
Class.forName(driver);
//(2)、与数据库建立连接
conn = DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
return false;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}
/*public Connection getConnection(){
try {
//初始化上下文
Context cxt = new InitialContext();
//获取与逻辑名相同的数据源对象
DataSource ds = (DataSource) cxt.lookup("java:comp/env/jdbc/orcl");
ds.getConnection();
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}*/
//增删改
public int executeUpdate(String sql,Object[] prames){
int updateRows=0;
getConnection();
try {
ps=conn.prepareStatement(sql);
//填充占位符
for (int i = 0; i < prames.length; i++) {
ps.setObject(i+1, prames[i]);
}
updateRows=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return updateRows;
}
//查询
public ResultSet executeSQL(String sql,Object[] prames){
getConnection();
try {
ps=conn.prepareStatement(sql);
//填充占位符
for (int i = 0; i < prames.length; i++) {
ps.setObject(i+1, prames[i]);
}
rs=ps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
//关闭资源
public boolean closeResource(){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
if(ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
return true;
}
}
至此,三种常用与数据库连接的方式结束,无论是哪一种,都是殊途同归,从上面可以看出,在执行查询时最后不需要关闭资源,但增删改必须要关闭资源。
(ps:小弟学识有限,如有谬论还请各位大神不吝赐教,感激不尽)