sqlite 增删改查加事务

jason0401

　　说明: 下面的代码演示了如何对 sqlite 数据库的操作..
　　注意:
　　1. 数据库名 pmm_cz 你可以任意修改成你自己的. 无论是否存在都可以. 因为如果存在, 则不会再创建. 如果不存在, 则新创建一个.
　　2. 表名也可以任意取. 表里的字段名也可以任意取. 我这里只是演示我自己的环境中的. 至于表的创建, 你可以使用 SQLite Expert Personal 这个可视化的编辑器创建.
　　3. 开发环境 myeclipse 8.5 + jdk 1.6
　　1) 创建一个 java project 或 web 都可以.
　　2) 在 http://www.zentus.com/sqlitejdbc/ 中下载 sqlite 驱动 jar 包, 然后添加到 myeclise 的 Builder path 即可.. 我下的是: sqlitejdbc-v056.jar

package blrise.emcs.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
public class SqliteTest {
/**
* 测试获取 Connection
* @throws Exception
*/
@Test
public void testGetConnection() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:d:pmm_cz.db");
System.out.println(conn);
}
/**
* 测试获取结果集
* @throws Exception
*/
@Test
public void getQueryAllData() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:d:pmm_cz.db");
String sql = "select * from WM_EQUI_TYPE";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("eq_type") + " :: " + rs.getString("eq_type_name"));
}
}
/**
* 测试插入数据
* @throws Exception
*/
@Test
public void testInsert() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:d:pmm_cz.db");
String sql = "insert into WM_EQUI_TYPE (eq_type, eq_type_name) values (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "4");
pstmt.setString(2, "ddd");
pstmt.execute();
System.out.println("Done.");
}
/**
* 测试删除数据
* @throws Exception
*/
@Test
public void testDelete() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:d:pmm_cz.db");
//String sql = "delete from WM_EQUI_TYPE where eq_type = ?";
String sql = "delete from WM_EQUI_TYPE";
PreparedStatement pstmt = conn.prepareStatement(sql);
//pstmt.setString(1, "4");
pstmt.execute();
System.out.println("Done.");
}
/**
* 测试修改数据
* @throws Exception
*/
@Test
public void testUpdate() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:d:pmm_cz.db");
String sql = "update WM_EQUI_TYPE set eq_type_name = ? where eq_type = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "test");
pstmt.setString(2, "3");
pstmt.execute();
System.out.println("Done.");
}
/**
* 测试批处理添加
* @throws Exception
*/
@Test
public void testBatch() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:d:pmm_cz.db");
String sql = "insert into WM_EQUI_TYPE (eq_type, eq_type_name) values (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int i=1; i<=10; i++) {
pstmt.setString(1, i + "type");
pstmt.setString(2, i + "typeName");
pstmt.addBatch();
}
pstmt.executeBatch();
System.out.println("Done.");
}
/**
* 测试事务
* @throws Exception
*/
@Test
public void testTransaction() throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:d:pmm_cz.db");
conn.setAutoCommit(false);
String sql = "insert into WM_EQUI_TYPE (eq_type, eq_type_name) values (?, ?)";
pstmt = conn.prepareStatement(sql);
for (int i=1; i<=10; i++) {
pstmt.setString(1, i + "type");
pstmt.setString(2, i + "typeName");
pstmt.addBatch();
if (i == 9) {
throw new RuntimeException();
}
}
pstmt.executeBatch();
System.out.println("Done.");
conn.commit();
} catch (Exception e) {
e.printStackTrace();
conn.rollback();
throw e;
} finally {
release(conn, pstmt, rs);
}
}
public void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
try {
if (conn != null) {
conn.close();
}
conn = null;
if (pstmt != null) {
pstmt.close();
}
pstmt = null;
if (rs != null) {
rs.close();
}
rs = null;
} catch (Exception e) {
e.printStackTrace();
}
}
}

　　1. 因为是单元测试, 并且声明了抛出异常所以没有对异常进行处理. 其实也不需要进行处理, try...catch 住了直接往外抛即可..
　　2. 其实没必要每个单元测试类中写获取连接之类的操作. 完全可以写一个工具类来实现.这里为了偷懒了. 全写一块了.
　　重点只是演示了 sqlite 的使用. 一个文件搞定. 搞多了文件, 也看不清.
　　应该没有什么问题了吧?