前言:
其实不论是ODBC还是JDBC都是用于连接和操纵数据库的、从性质上来说一个是小米,一个是锤子、都是手机、只不过一个是NET一个是JAVA的这次就说下JDBC连接数据库的步骤和代码。
步骤
1 加载驱动、创建数据库连接Connection
Connection是java.sql包中的、用于创建程序与数据库的连接、会话
XML文件配置
<span style="font-size:18px;"><?xml version="1.0" encoding="UTF-8"?>
<config>
<db-info>
<driver-name>oracle.jdbc.driver.OracleDriver</driver-name>
<url>jdbc:oracle:thin:@192.168.24.95:1521:ORCL</url>
<user-name>chenchen</user-name>
<password>chen</password>
</db-info>
</config>
</span>
封装好的程序用于返回一个Connection的会话连接对象
<span style="font-size:18px;">/**
* 封装数据库常用操作
*
* @author chen
*
*/
public class DbUtil {
/**
* 取得Connection
*
* @return
*/
public static Connection getConnection() {
// 加载驱动,建立连接
Connection conn = null;
try {
JdbcConfig jdbcConfig = XMLConfigReader.getInstance()
.getJdbcConfig();
// orcl为oracle数据库中的数据库名,localhost表示连接本机的oracle数据库
// 1521为连接的端口号
Class.forName(jdbcConfig.getDriverName());
conn = DriverManager.getConnection(jdbcConfig.getUrl(),
jdbcConfig.getUserName(), jdbcConfig.getPassword());
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
// 返回 conn
return conn;
}
//释放Connection资源
public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//释放Statement资源
public static void close(Statement pastmt) {
if (pastmt != null) {
try {
pastmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//释放Statement资源
public static void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}</span>
<span style="font-size:18px;"><span style="white-space:pre"> </span>}</span>
<span style="font-size:18px;">}</span>
因为分步骤代码会看不清、所以都粘贴上来了、具体的都写在注释里了
<span style="font-size:18px;">/**
* 取得分销商级别列表
*
*/
public List<ClientLevel> findClientLevelList() {
//2创建SQL语句
String sql = "select id, name from t_data_dict where category='A'";
//声明Connection和PreparedStatement对象
Connection conn = null;
PreparedStatement pstmt = null;
//用于接收返回值
ResultSet rs = null;
//存储查出的数据
List<ClientLevel> list = new ArrayList<ClientLevel>();
try {
//获取conn数据库连接
conn = DbUtil.getConnection();
//执行SQL语句
pstmt = conn.prepareStatement(sql);
//取得结果-如果是删除或修改用pstmt.executeUpdate()方法
rs = pstmt.executeQuery();
//循环赋值
while (rs.next()) {
ClientLevel c1 = new ClientLevel();
c1.setId(rs.getString("id"));
c1.setName(rs.getString("name"));
list.add(c1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//释放资源
DbUtil.close(conn);
DbUtil.close(pstmt);
}
//返回
return list;
}</span>
带参数的例子
<span style="font-size:18px;">/**
* 修改用户
*/
public void modifyUser(User user) {
StringBuffer sbSqlBuffer = new StringBuffer();
sbSqlBuffer.append("update t_user ").append("set user_name = ?,")
.append("password = ?,").append("contact_tel = ?,")
.append("email = ? ").append("where user_id = ? ");
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sbSqlBuffer.toString());
pstmt.setString(1, user.getUserName());
pstmt.setString(2, user.getPassword());
pstmt.setString(3, user.getContactTel());
pstmt.setString(4, user.getEmail());
pstmt.setString(5, user.getUserId());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtil.close(conn);
DbUtil.close(pstmt);
}
}
</span>
对于具体的对Connection中的prepareStatement与createStatement的区别,好处ResultSet等、下篇博客见= =附录一张思维导图
——————————————————chenchen—————————————————