sqlite 增删改查加事务

说明: 下面的代码演示了如何对 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 的使用. 一个文件搞定. 搞多了文件, 也看不清.

应该没有什么问题了吧?