首先需要将oracle的jar包导入到项目中,将E:\oracle\product\10.2.0\db_1\jdbc\lib路径下的classes12.jar包都导入项目。
一、最简单的使用方法:
package blog;
import java.sql.*;
import java.util.Properties;
public class AddStudentDao implements IAddStudentDao {
@Override
public boolean addStudent(AddStudentForm studentForm) {
//使用JDBC操作数据
Connection conn = null;
PreparedStatement pstmt = null;
//1、首先注册具体数据库的驱动包
try {
Driver myDriver = (Driver) Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
//2、利用DriverManager获取数据库连接对象Connection
Properties properties = new Properties();
properties.put("user", "xzf");
properties.put("password", "1234");
conn = myDriver.connect("jdbc:oracle:thin:@127.0.0.1:1521:orcl",properties);
String sql = " insert into students values(? ,? ,? ,?)";
System.out.println("major: " + studentForm.getMajor());
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,studentForm.getsId());
pstmt.setString(2, studentForm.getsName());
pstmt.setString(3, studentForm.getMajor());
pstmt.setInt(4, studentForm.getScore());
int count = pstmt.executeUpdate();
System.out.println("影响行数:" + count);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(pstmt!=null)pstmt.close();
if(conn!=null)conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return true;
}
}
二、使用连接池DBUtil:
1、先创建DBUtil类:
package blog.util;
import java.sql.Connection;
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;
import cn.itcast.exception.DBException;
public class DBUtil {
private static DataSource ds = null;
/**
* 从数据库连接池获得一个数据库连接
* @return 数据库连接
* @throws DBException
*/
public static Connection getConnection() throws DBException {
//用数据库连接池的方式实现,JNDI
try {
if(ds == null){
Context context = new InitialContext();
ds = (DataSource) context.lookup("java:comp/env/jdbc/orcl");
}
return ds.getConnection();
} catch (NamingException e) {
throw new DBException("数据库连接池查找失败", e);
} catch (SQLException e) {
throw new DBException("获取数据库连接异常", e);
}
}
public static PreparedStatement getPreparedStatement(Connection conn, String sql) throws DBException {
PreparedStatement pstmt = null;
try {
if (conn != null) {
pstmt = conn.prepareStatement(sql);
}
} catch (SQLException e) {
throw new DBException("创建执行语句失败", e);
}
return pstmt;
}
public static PreparedStatement getPreparedStatement(Connection conn, String sql, int autoGenereatedKeys) throws DBException {
PreparedStatement pstmt = null;
try {
if (conn != null) {
pstmt = conn.prepareStatement(sql, autoGenereatedKeys);
}
} catch (SQLException e) {
throw new DBException("创建执行语句失败", e);
}
return pstmt;
}
public static Statement getStatement(Connection conn) throws DBException {
Statement stmt = null;
try {
if (conn != null) {
stmt = conn.createStatement();
}
} catch (SQLException e) {
throw new DBException("创建执行语句失败", e);
}
return stmt;
}
public static ResultSet getResultSet(Statement stmt, String sql) throws DBException {
ResultSet rs = null;
try {
if (stmt != null) {
rs = stmt.executeQuery(sql);
}
} catch (SQLException e) {
throw new DBException("获得查询结果集失败:" + sql, e);
}
return rs;
}
public static void executeUpdate(Statement stmt, String sql) throws DBException {
try {
if (stmt != null) {
stmt.executeUpdate(sql);
}
} catch (SQLException e) {
throw new DBException("更新失败:" + sql, e);
}
}
/**
* 归还数据库连接
* @param conn 数据库连接实例
* @throws DBException
*/
public static void close(Connection conn) throws DBException {
try {
if (conn != null) {
conn.close(); //把数据库连接归还到数据库连接池,并不是真正的断开数据库的连接
}
} catch (SQLException e) {
throw new DBException("关闭数据库连接异常", e);
}
}
public static void close(Statement stmt) throws DBException {
try {
if (stmt != null) {
stmt.close();
stmt = null;
}
} catch (SQLException e) {
throw new DBException("关闭数据库语句异常", e);
}
}
public static void close(ResultSet rs) throws DBException {
try {
if (rs != null) {
rs.close();
rs = null;
}
} catch (SQLException e) {
throw new DBException("关闭数据库结果集异常", e);
}
}
}
2、在META-INF下创建一个Context.xml文件,内容如下:
<Context reloadable="true">
<Resource name="jdbc/orcl" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="xzf" password="1234"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@127.0.0.1:1521:orcl">
</Resource>
</Context>
3、在web.xml中加入如下内容:
<resource-ref>
<res-ref-name>jdbc/orcl</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
4、将oracle的classes12.jar包加入tomcat的lib目录下。
这样就可以使用DBUtil了:Connection conn =DBUtil.getConnection();