java连接使用sqlite，java连接sqlite

java连接使用sqlite，java连接sqlite

本文只介绍jdbc连接sqlite

测试环境：myeclipse8.5+jdk6+tomcat6,所用jar包：sqlitejdbc-v033-nested.jar

请自行百度下。

1，连接JDBC的URL格式为jdbc:sqlite:/path。特别声明：path的盘符路径必须为小写，否则报错：

例如：jdbc:sqlite://E:/sqlite/test.db。报错如下：

        

这里的path为指定到SQLite数据库文件的路径，例如：jdbc:sqlite://e:/sqlite/test.db。并且路径为unix路径格式(反斜杠用/)。

2，给出示例的完整代码。

import java.sql.*;

import org.sqlite.JDBC;

import com.jingwang.utils.FinalString;

public class Main {

	public static final String CREATE_TABLE_SONGINFO = "CREATE TABLE if not exists `jwsonginfo` ( `JWSongID` int(11) NOT NULL ,"
		  +"`CAVCASongID` varchar(50) NOT NULL,"
		  +"`SongName` varchar(256) NOT NULL,"
		  +"`SongNameShort` varchar(50) NOT NULL,"
		  +"`SongAddTimeSec` bigint(20) NOT NULL default '0',"
		  +"`SongIsRecommend` smallint(6) NOT NULL,"
		  +"`SongSingerID` int(11) NOT NULL,"
		  +"`SongZoneID` int(11) NOT NULL,"
		  +"`SongThemeID` int(11) NOT NULL,"
		  +"`SongSingStyleID` int(11) NOT NULL,"
		  +"`SongInLocal` smallint(6) NOT NULL default '0',"
		  +"`SongCodeValue` int(11) NOT NULL,"
		  +"`SongGroupID` int(11) NOT NULL, "
		  +"`ServerGroupID` int(11) NOT NULL,"
		  +"`SongFileName` varchar(256) NOT NULL default 'file',"
		  +"`SongChecked` int(11) NOT NULL default '0');";  
	/**
	 * @param args
	 * @throws Exception 
	 */
	public static void main(String[] args) throws Exception {
		try{
			//1，保证SQLite数据库文件的路径首字符为小写，并且路径为unix路径
			String thisPath = "E:\\Abc\\xxxx.db";
			String str1=thisPath.substring(0,1).toLowerCase();	//直接将字符串第一个字母小写
			String str2=thisPath.substring(1,thisPath.length());//截取字符串第二个以后
			thisPath=str1+str2;
			String sql = "jdbc:sqlite://"+ thisPath.replace('\\', '/')+FinalString.DB_NAME;//windows && linux都适用
			System.out.println(sql);

			 //2，连接SQLite的JDBC
			 Class.forName("org.sqlite.JDBC"); 

			 //建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下自动创建
			 Connection conn = DriverManager.getConnection(sql);

			 //3,创建表
			 Statement stat = conn.createStatement();
			 stat.executeUpdate( CREATE_TABLE_SONGINFO );
			 
			 //4,插入一条数据
			 PreparedStatement prep = conn.prepareStatement(
		      "insert into jwsonginfo values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");

			    prep.setString(1, "2");
			    prep.setString(2, "1");
			    prep.setString(3, "xx");
			    prep.setString(4, "xx");
			    prep.setString(5, "xx");
			    prep.setString(6, "1");
			    prep.setString(7, "1");
			    prep.setString(8, "1");
			    prep.setString(9, "1");
			    prep.setString(10, "1");
			    prep.setString(11, "1");
			    prep.setString(12, "1");
			    prep.setString(13, "1");
			    prep.setString(14, "1");
			    prep.setString(15, "1");
			    prep.setString(16, "1");
			    prep.addBatch();
			    
			    conn.setAutoCommit(false);
			    prep.executeBatch();
			    conn.setAutoCommit(true);

			 ResultSet rs =  stat.executeQuery("select * from jwsonginfo;"); 
			 while (rs.next()) { //将查询到的数据打印出来
				 System.out.print("name = " + rs.getString("CAVCASongID") + " "); //列属性一
				 System.out.println("salary = " + rs.getString("SongFileName")); //列属性二 
			 }

			 rs.close();
			 conn.close(); //结束数据库的连接 
		 }
		 catch( Exception e )
		 {
			 e.printStackTrace ( );
		 }
	}

}

3，再给出个增改查删的类SQLiteCRUD,使用更方便：

package com.jingwang.utils;
import java.sql.*;

public class SQLiteCRUD {
	private Connection connection ;
		
    public SQLiteCRUD(Connection connection) {  
        this.connection = connection ;  
    } 
    
    /** 
     * @param sql 
     * @return boolean 
     */  
    public boolean createTable(String sql){  
        Statement stmt = null ;  
        try{  
        	stmt = this.connection.createStatement() ; 
            stmt.executeUpdate(sql) ;  
            return true ;  
        }catch (Exception e) {  
            System.out.println("create table: " + e.getLocalizedMessage());  
            connectionRollback(connection) ;  
            return false ;  
        }  
    }
    
    /** 
     * insert一条多个字段值的数据
     * @param table 表名
     * @param params 多个字段值
     * @return boolean 
     */  
    public boolean insert(String table, String[] params){  
        Statement stmt = null ;  
        String sql = "insert into " + table  + " values('";  
        for(int i = 0 ; i < params.length ;i++){  
            if(i == (params.length - 1)){  
                sql += (params[i] + "');") ;  
            }else{  
                sql += (params[i] + "', '") ;  
            }  
        }  
        System.out.println(sql);  
        try{  
            stmt = this.connection.createStatement() ;  
            stmt.executeUpdate(sql) ;
            if(!connection.isClosed()){
            	connection.close();
            }
            return true ;  
        }catch (Exception e) {  
            System.out.println("������" + table + "���ʱ�쳣 : " + e.getLocalizedMessage());  
            connectionRollback(connection) ;  
            return false ;  
        }  
    }  
      
    /** 
     * @param table 
     * @param keyParam 
     * @param keyField  
     * @param fields  
     * @param params  
     * @return boolean 
     */  
    public boolean update(String table, String keyParam, String keyField, String[] fields, String[] params){  
        Statement stmt = null ;  
        String sql = "update " + table + " set " ;  
        for(int i = 0 ; i < fields.length ; i++){  
            if(i == (fields.length - 1)){  
                sql += (fields[i] + "='" + params[i] + "' where " + keyField + "='" + keyParam +"';") ;  
            }else{  
                sql += (fields[i] + "='" + params[i] + "', ") ;  
            }  
        }  
        System.out.println(sql);  
        try{  
            stmt = this.connection.createStatement() ;  
            stmt.executeUpdate(sql) ;  
            return true ;  
        }catch (Exception e) {  
            System.out.println( e.getLocalizedMessage());  
            connectionRollback(connection) ;  
            return false ;  
        }  
          
    }  
      
    /** 
     * @param table 
     * @param key 
     * @param keyValue 
     * @return boolean 
     */  
    public boolean delete(String table, String key, String keyValue){  
        Statement stmt = null ;  
        String sql = "delete from " + table + " where " + key + "='" + keyValue + "';" ;  
        System.out.println(sql);  
        try{  
            stmt = this.connection.createStatement() ;  
            stmt.executeUpdate(sql) ;  
            return true ;  
        }catch (Exception e) {  
            System.out.println( e.getLocalizedMessage());  
            connectionRollback(connection) ;  
            return false ;  
        }  
    }  
    
    private void connectionRollback(Connection connection){  
        try {  
            connection.rollback() ;  
        } catch (SQLException e) {  
            System.out.println(e.getLocalizedMessage()) ;  
        }  
    }  
}

以上有参考其他兄弟姐妹的文章，一并谢过。