使用JDBC进行数据库连接并操作数据库中的数据的基本步骤有加载JDBC驱动程序、建立数据库连接、创建Statement对象、执行SQL语句、处理返回结果、关闭创建的对象。下面我就对这6步分别进行整理，最后附上JDBC的具体连接实例方法。
1、加载驱动
在与某一特定数据库建立连接之前，应该首先下载一种可用的JDBC驱动程序。加载驱动程序的一种简单易于实现的方法是使用Class.forName()方法进行显示加载：
即：Class.forName("DriverName");
其中DriverName是要进行加载JDBC驱动程序名称。
例如：加载MySql方法 Class.forName( "org.gjt.mm.mysql.Driver" );
加载oracle方法 Class.forName( "oracle.jdbc.driver.OracleDriver" );
加载SQL Server方法Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
2、建立连接
在使用JDBC操作数据库之前必须建立一个连接，这时需要使用到DriverMannager 类中的getConnection（）方法，使用语法格式如下：
DriverManager.getConnection(String URL,String USER,String PWD);
该方法返回一个Connetion对象。Connection是一个接口，表示与数据库的连接并拥有创建SQL语句的方法，用于完成基本的SQL操作。
3、创建Statement对象
建立连接后，可以向通过此链接向目标数据库发送SQL语句，在发送语句之前，必须创建Statement对象，负责将SQL语句发送给数据库。语法格式如下：
Statement smt = conn.createStatement();
4、执行SQl语句并得到结果
获取到Statement对象后，就可以使用该对象的executeQuery()方法来执行SQL语句，语法格式如下：
ResultSet rs = stm.executeQuery(String sql);
5、关闭连接并释放资源
使用完之后，为防止资源浪费需要对访问数据库时建立的对象进行关闭。JDBC关闭次序为先关Result对象，再关Statement对象，最后关Connection对象。
下面将我JDBC连接SQL Server实例进行整理记录：
//DBConnection.java 数据库连接和关闭公共类
package com.hp.util;
import java.sql.*;
/**
* 数据库连接和关闭公共类
*
* @author Hello_Error
*
*/
public class DBConnection {
private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String URL = "jdbc:sqlserver://localhost:1433;databasename=NRS";
private static final String USER = "sa";
private static final String PWD = "123";
// 注册驱动
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 获得连接
public static Connection getConn() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL,USER,PWD);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//关闭连接
public static void closeConn(Connection conn){
if(conn != null){
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//关闭执行对象
public static void closeStatement(Statement stmt){
if(stmt != null){
try {
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//关闭结果集
public static void closeResultSet(ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
//Test.java 测试类 测试数据库是否连接成功
package com.hp.util;
import java.sql.Connection;
/**
* 测试数据库连接是否成功
*
* @author Hello_Error
*
*/
public class Test {
public static void main(String[] args) {
Connection conn = DBConnection.getConn();
System.out.println(conn);
}
}
运行Test.java返回ConnectionID号表示连接成功。如下图：
此外，附加对数据库的增、删、改、查的方法例子
package com.hp.dao.impl;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import com.hp.bean.Course;
import com.hp.bean.CourseView;
import com.hp.dao.SysCourseDao;
import com.hp.util.DBConnection;
/**
* 数据库连接的增删改查方法实现
*
* @author Hello_Error
*
*/
public class SysCourseDaoImpl implements SysCourseDao {
//添加
public int addCourse(Course cou) {
Connection conn = null;
PreparedStatement pstmt = null;
int row = 0;
String sql = "insert into t_course(coursename,credit,ctime,caddress,teacher,limitnumber)values(?,?,?,?,?,?)";
try {
conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, cou.getCoursename());
pstmt.setInt(2, cou.getCredit());
pstmt.setString(3, cou.getCtime());
pstmt.setString(4, cou.getCaddress());
pstmt.setInt(5, cou.getTeacher());
pstmt.setInt(6, cou.getLimitnumber());
row = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBConnection.closeStatement(pstmt);
DBConnection.closeConn(conn);
}
return row;
}
//删除
@Override
public int deleteCourse(int id) {
Connection conn = null;
PreparedStatement pstmt = null;
int row = 0;
String sql = "delete t_course where id=?";
try {
conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
row = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBConnection.closeStatement(pstmt);
DBConnection.closeConn(conn);
}
return row;
}
//修改
public int updateCourse(Course cou) {
Connection conn = null;
PreparedStatement pstmt = null;
int row = 0;
String sql = "update t_course set coursename=?,credit=?,ctime=?,caddress=?,teacher=?,limitnumber=? where id=?";
try {
conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, cou.getCoursename());
pstmt.setInt(2, cou.getCredit());
pstmt.setString(3, cou.getCtime());
pstmt.setString(4, cou.getCaddress());
pstmt.setInt(5, cou.getTeacher());
pstmt.setInt(6, cou.getLimitnumber());
pstmt.setInt(7, cou.getId());
row = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBConnection.closeStatement(pstmt);
DBConnection.closeConn(conn);
}
return row;
}
//根据名查找
public CourseView findCouByName(String coursename) {
CourseView couv = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select c.id,c.coursename,c.credit,c.ctime,c.caddress,t.tname,c.limitnumber,c.truenumber "
+ "from t_course c inner join t_teacher t on c.teacher=t.tid where coursename=?";
try {
conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, coursename);
rs = pstmt.executeQuery();
if (rs.next()) {
couv = new CourseView();
couv.setId(rs.getInt(1));
couv.setCoursename(rs.getString(2));
couv.setCredit(rs.getInt(3));
couv.setCtime(rs.getString(4));
couv.setCaddress(rs.getString(5));
couv.setTname(rs.getString(6));
couv.setLimitnumber(rs.getInt(7));
couv.setTruenumber(rs.getInt(8));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBConnection.closeStatement(pstmt);
DBConnection.closeConn(conn);
}
return couv;
}
//查看所有
public List<CourseView>findAllCourse() {
List<CourseView> list = new ArrayList<CourseView>();
DBConnection db = new DBConnection();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select c.id,c.coursename,c.credit,c.ctime,c.caddress,t.tname,c.limitnumber,c.truenumber "
+ "from t_course c inner join t_teacher t on c.teacher=t.tid";
try {
conn = db.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
CourseView couv = new CourseView();
couv.setId(rs.getInt(1));
couv.setCoursename(rs.getString(2));
couv.setCredit(rs.getInt(3));
couv.setCtime(rs.getString(4));
couv.setCaddress(rs.getString(5));
couv.setTname(rs.getString(6));
couv.setLimitnumber(rs.getInt(7));
couv.setTruenumber(rs.getInt(8));
list.add(couv);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBConnection.closeStatement(pstmt);
DBConnection.closeConn(conn);
}
return list;
}
}