目次

本体ダウンロード

コマンドライン対応のWindows版を落としてくる。

DLLでもいいんだけどね。

解凍すると、

sqlite3.exe

というファイルがある。

ここでは、下記のフォルダを作成してまとめて格納することにする。

c:\sqlite\

URL

http://www.sqlite.org/download.html

空のデータベース作成

Javaプログラムから

存在しないDBにコネクションを張ろうとすると自動的に作成されます。

    Class.forName("org.sqlite.JDBC");
    Connection c = DriverManager.getConnection("jdbc:sqlite:/c:/sqlite/testdatabase3");

コマンドプロンプトから

cd c:\sqlite
C:\sqlite>sqlite3 testdatabase

SQLite version 3.6.23.1

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

と表示される。

DDLを入力する。

create table testtable(
col1 int primary key,
col2 char(128),
col3 char(128)
);

コマンド

テーブル一覧を見る。

.tables

SQLite終了

.quit

JDBCドライバインストール

2箇所開発しているところがあるってことなのかな?でも、JDBCが動くサイトと、動かないサイトがあるんだよね。

OKなJDBC

http://www.zentus.com/sqlitejdbc/

ちゃんと動作したバージョンは下記のバージョン

sqlitejdbc-v056.jar

NGなJDBC

下記のは使い物にならんかった。正式版っぽい顔してけしからん。

http://www.db.is.kyushu-u.ac.jp/rinkou/sqlite/sqlitejava.html

単純に最新版をEclipseから外部JARファイル追加としても動作しない。

こんなエラーが出る

java.lang.UnsupportedClassVersionError: org/sqlite/JDBC (Unsupported major.minor version 49.0)

かんべんしてよ。

いっそのこと、SQLite3とJDBCドライバまとめて出しちゃえば?

っておもったよ。

動作確認用Javaサンプル

package sqlite;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class HelloSQLite
{
public static void main(String[] args) throws ClassNotFoundException, SQLException
{
    Class.forName("org.sqlite.JDBC");
    Connection c = DriverManager.getConnection("jdbc:sqlite:/c:/sqlite/testdatabase");
}
}

メモリ上にテーブルを作成する場合

Connection c = DriverManager.getConnection("jdbc:sqlite::memory:");

編集ツール

Windows と Linux で使える編集ツールです。

Windows で使える編集ツールです。

Firefox で使える編集ツールです。

Adobe AIR で使える編集ツールです。

SQLite が認識できる SQL

http://net-newbie.com/sqlite/lang.html

テストツール

※書きかけです。

テストツールを作成する際、ある程度の決まりごとがあるので、それらのコードをまとめておく。

設計方針としては、極力ローカル変数で宣言しなくても、目的のコードを記述できるようにする。

コード

作りかけではあるが、クラスのデザインの方向性を示せるレベルだとは思っています。

Ary

package base;
import java.util.ArrayList;

public class Ary extends ArrayList {

}

Column

package base;

import java.sql.ResultSet;
import java.sql.SQLException;


abstract public class Column {
   private String TABLE_CAT;
   private String TABLE_SCHEM;
   private String TABLE_NAME;
   private String COLUMN_NAME;
   private int DATA_TYPE;
   private String TYPE_NAME;
   private int COLUMN_SIZE;
   private int BUFFER_LENGTH;
   private int DECIMAL_DIGITS;
   private int NUM_PREC_RADIX;
   private int NULLABLE;
   private String REMARKS;
   private String COLUMN_DEF;
   private int SQL_DATA_TYPE;
   private int SQL_DATETIME_SUB;
   private int CHAR_OCTET_LENGTH;
   private int ORDINAL_POSITION;
   private String IS_NULLABLE;
   private int JDBC_DATA_TYPE;
   private int PSEUDO_COLUMN;
   private String COLUMN_TEXT;
   
   
   public int getBUFFER_LENGTH() {
       return BUFFER_LENGTH;
   }
   public void setBUFFER_LENGTH(int buffer_length) {
       BUFFER_LENGTH = buffer_length;
   }
   public int getCHAR_OCTET_LENGTH() {
       return CHAR_OCTET_LENGTH;
   }
   public void setCHAR_OCTET_LENGTH(int char_octet_length) {
       CHAR_OCTET_LENGTH = char_octet_length;
   }
   public String getCOLUMN_DEF() {
       return COLUMN_DEF;
   }
   public void setCOLUMN_DEF(String column_def) {
       COLUMN_DEF = column_def;
   }
   public String getCOLUMN_NAME() {
       return COLUMN_NAME;
   }
   public void setCOLUMN_NAME(String column_name) {
       COLUMN_NAME = column_name;
   }
   public int getCOLUMN_SIZE() {
       return COLUMN_SIZE;
   }
   public void setCOLUMN_SIZE(int column_size) {
       COLUMN_SIZE = column_size;
   }
   public String getCOLUMN_TEXT() {
       return COLUMN_TEXT;
   }
   public void setCOLUMN_TEXT(String column_text) {
       COLUMN_TEXT = column_text;
   }
   public int getDATA_TYPE() {
       return DATA_TYPE;
   }
   public void setDATA_TYPE(int data_type) {
       DATA_TYPE = data_type;
   }
   public int getDECIMAL_DIGITS() {
       return DECIMAL_DIGITS;
   }
   public void setDECIMAL_DIGITS(int decimal_digits) {
       DECIMAL_DIGITS = decimal_digits;
   }
   public String getIS_NULLABLE() {
       return IS_NULLABLE;
   }
   public void setIS_NULLABLE(String is_nullable) {
       IS_NULLABLE = is_nullable;
   }
   public int getJDBC_DATA_TYPE() {
       return JDBC_DATA_TYPE;
   }
   public void setJDBC_DATA_TYPE(int jdbc_data_type) {
       JDBC_DATA_TYPE = jdbc_data_type;
   }
   public int getNULLABLE() {
       return NULLABLE;
   }
   public void setNULLABLE(int nullable) {
       NULLABLE = nullable;
   }
   public int getNUM_PREC_RADIX() {
       return NUM_PREC_RADIX;
   }
   public void setNUM_PREC_RADIX(int num_prec_radix) {
       NUM_PREC_RADIX = num_prec_radix;
   }
   public int getORDINAL_POSITION() {
       return ORDINAL_POSITION;
   }
   public void setORDINAL_POSITION(int ordinal_position) {
       ORDINAL_POSITION = ordinal_position;
   }
   public int getPSEUDO_COLUMN() {
       return PSEUDO_COLUMN;
   }
   public void setPSEUDO_COLUMN(int pseudo_column) {
       PSEUDO_COLUMN = pseudo_column;
   }
   public String getREMARKS() {
       return REMARKS;
   }
   public void setREMARKS(String remarks) {
       REMARKS = remarks;
   }
   public int getSQL_DATA_TYPE() {
       return SQL_DATA_TYPE;
   }
   public void setSQL_DATA_TYPE(int sql_data_type) {
       SQL_DATA_TYPE = sql_data_type;
   }
   public int getSQL_DATETIME_SUB() {
       return SQL_DATETIME_SUB;
   }
   public void setSQL_DATETIME_SUB(int sql_datetime_sub) {
       SQL_DATETIME_SUB = sql_datetime_sub;
   }
   public String getTABLE_CAT() {
       return TABLE_CAT;
   }
   public void setTABLE_CAT(String table_cat) {
       TABLE_CAT = table_cat;
   }
   public String getTABLE_NAME() {
       return TABLE_NAME;
   }
   public void setTABLE_NAME(String table_name) {
       TABLE_NAME = table_name;
   }
   public String getTABLE_SCHEM() {
       return TABLE_SCHEM;
   }
   public void setTABLE_SCHEM(String table_schem) {
       TABLE_SCHEM = table_schem;
   }
   public String getTYPE_NAME() {
       return TYPE_NAME;
   }
   public void setTYPE_NAME(String type_name) {
       TYPE_NAME = type_name;
   }
   
   public String makeDDL() {
       StringBuffer ret = new StringBuffer();
       ret.append(getCOLUMN_NAME());
       ret.append(" ");
       ret.append(getColumntypeInstance().getDDL());
       return ret.toString();
   }
   
   public void printinfo(){
       System.out.println("TABLE_CAT\t:" + getTABLE_CAT());
       System.out.println("TABLE_SCHEM\t:" + getTABLE_SCHEM());
       System.out.println("TABLE_NAME\t:" + getTABLE_NAME());
       System.out.println("COLUMN_NAME\t:" + getCOLUMN_NAME());
       System.out.println("DATA_TYPE\t:" + getDATA_TYPE());
       System.out.println("TYPE_NAME\t:" + getTYPE_NAME());
       System.out.println("COLUMN_SIZE\t:" + getCOLUMN_SIZE());
       System.out.println("BUFFER_LENGTH\t:" + getBUFFER_LENGTH());
       System.out.println("DECIMAL_DIGITS\t:" + getDECIMAL_DIGITS());
       System.out.println("NUM_PREC_RADIX\t:" + getNUM_PREC_RADIX());
       System.out.println("NULLABLE\t:" + getNULLABLE());
       System.out.println("NULLABLE\t:" + getNULLABLE());
       System.out.println("COLUMN_DEF\t:" + getCOLUMN_DEF());
       System.out.println("SQL_DATA_TYPE\t:" + getSQL_DATA_TYPE());
       System.out.println("SQL_DATETIME_SUB\t:" + getSQL_DATETIME_SUB());
       System.out.println("CHAR_OCTET_LENGTH\t:" + getCHAR_OCTET_LENGTH());
       System.out.println("ORDINAL_POSITION\t:" + getORDINAL_POSITION());
       System.out.println("IS_NULLABLE\t:" + getIS_NULLABLE());
       System.out.println("JDBC_DATA_TYPE\t:" + getJDBC_DATA_TYPE());
       System.out.println("PSEUDO_COLUMN\t:" + getPSEUDO_COLUMN());
       System.out.println("COLUMN_TEXT\t:" + getCOLUMN_TEXT());
   }
   
   abstract public IColumntype getColumntypeInstance();
   public void setByRs(ResultSet rs) {
       try {
           setTABLE_CAT(ColumnUtil.getTABLE_CAT(rs));
           setTABLE_SCHEM(ColumnUtil.getTABLE_SCHEM(rs));
           setTABLE_NAME(ColumnUtil.getTABLE_NAME(rs));
           setCOLUMN_NAME(ColumnUtil.getCOLUMN_NAME(rs));
           setDATA_TYPE(ColumnUtil.getDATA_TYPE(rs));
           setTYPE_NAME(ColumnUtil.getTYPE_NAME(rs));
           setCOLUMN_SIZE(ColumnUtil.getCOLUMN_SIZE(rs));
           setBUFFER_LENGTH(ColumnUtil.getBUFFER_LENGTH(rs));
           setDECIMAL_DIGITS(ColumnUtil.getDECIMAL_DIGITS(rs));
           setNUM_PREC_RADIX(ColumnUtil.getNUM_PREC_RADIX(rs));
           setNULLABLE(ColumnUtil.getNULLABLE(rs));
           setREMARKS(ColumnUtil.getREMARKS(rs));
           setCOLUMN_DEF(ColumnUtil.getCOLUMN_DEF(rs));
           setSQL_DATA_TYPE(ColumnUtil.getSQL_DATA_TYPE(rs));
           setSQL_DATETIME_SUB(ColumnUtil.getSQL_DATETIME_SUB(rs));
           setCHAR_OCTET_LENGTH(ColumnUtil.getCHAR_OCTET_LENGTH(rs));
           setORDINAL_POSITION(ColumnUtil.getORDINAL_POSITION(rs));
           setBUFFER_LENGTH(ColumnUtil.getBUFFER_LENGTH(rs));
           setIS_NULLABLE(ColumnUtil.getIS_NULLABLE(rs));
           setJDBC_DATA_TYPE(ColumnUtil.getJDBC_DATA_TYPE(rs));
           setPSEUDO_COLUMN(ColumnUtil.getPSEUDO_COLUMN(rs));
           setCOLUMN_TEXT(ColumnUtil.getCOLUMN_TEXT(rs));
       } catch (SQLException e) {
       }
   }
   public void setByColumn(Column item) {
       setTABLE_CAT(item.getTABLE_CAT());
       setTABLE_SCHEM(item.getTABLE_SCHEM());
       setTABLE_NAME(item.getTABLE_NAME());
       setCOLUMN_NAME(item.getCOLUMN_NAME());
       setDATA_TYPE(convDATA_TYPE(item));
       setTYPE_NAME(item.getTYPE_NAME());
       setCOLUMN_SIZE(item.getCOLUMN_SIZE());
       setBUFFER_LENGTH(item.getBUFFER_LENGTH());
       setDECIMAL_DIGITS(item.getDECIMAL_DIGITS());
       setNUM_PREC_RADIX(item.getNUM_PREC_RADIX());
       setNULLABLE(item.getNULLABLE());
       setREMARKS(item.getREMARKS());
       setCOLUMN_DEF(item.getCOLUMN_DEF());
       setSQL_DATA_TYPE(item.getSQL_DATA_TYPE());
       setSQL_DATETIME_SUB(item.getSQL_DATETIME_SUB());
       setCHAR_OCTET_LENGTH(item.getCHAR_OCTET_LENGTH());
       setORDINAL_POSITION(item.getORDINAL_POSITION());
       setBUFFER_LENGTH(item.getBUFFER_LENGTH());
       setIS_NULLABLE(item.getIS_NULLABLE());
       setJDBC_DATA_TYPE(item.getJDBC_DATA_TYPE());
       setPSEUDO_COLUMN(item.getPSEUDO_COLUMN());
       setCOLUMN_TEXT(item.getCOLUMN_TEXT());
   }
   abstract public int convDATA_TYPE(Column item);
}

ColumnDB2JDBC

package base;
public class ColumnDB2JDBC extends Column {
   public static final int DATA_TYPE_BIGINT = -5;
   public static final int DATA_TYPE_INTEGER = 4;
   public static final int DATA_TYPE_SMALLINT = 5;
   public static final int DATA_TYPE_DECIMAL = 3;
   public static final int DATA_TYPE_NUMERIC = 2;
   public static final int DATA_TYPE_DOUBLE_PRECISION = 8;
   public static final int DATA_TYPE_REAL = 7;
   public static final int DATA_TYPE_CHARACTER = 1;
   public static final int DATA_TYPE_CHARACTER_FOR_BIT_DATA = -2;
   public static final int DATA_TYPE_VARCHAR = 12;
   public static final int DATA_TYPE_VARCHAR_FOR_BIT_DATA = -3;
   public static final int DATA_TYPE_CLOB = 40;
   public static final int DATA_TYPE_GRAPHIC = -95;
   public static final int DATA_TYPE_VARGRAPHIC = -96;
   public static final int DATA_TYPE_DBCLOB = -350;
   public static final int DATA_TYPE_BINARY = -2;
   public static final int DATA_TYPE_VARBINARY = -3;
   public static final int DATA_TYPE_BLOB =30;
   public static final int DATA_TYPE_DATE = 91;
   public static final int DATA_TYPE_TIME = 92;
   public static final int DATA_TYPE_TIMESTAMP = 93;
   public static final int DATA_TYPE_DATALINK = 70;
   public static final int DATA_TYPE_ROWID = -100;
   public static final int DATA_TYPE_DISTINCT = 17;
   public IColumntype getColumntypeInstance() {
       // TODO 自動生成されたメソッド・スタブ
       return null;
   }
   
   public int convDATA_TYPE(Column item) {
       return -1;
   }    
}

ColumnDB2ODBC

package base;

public class ColumnDB2ODBC extends Column {
   public static final int DATA_TYPE_BIGINT = -5;
   public static final int DATA_TYPE_INTEGER = 4;
   public static final int DATA_TYPE_SMALLINT = 5;
   public static final int DATA_TYPE_DECIMAL = 3;
   public static final int DATA_TYPE_NUMERIC = 2;
   public static final int DATA_TYPE_DOUBLE_PRECISION = 8;
   public static final int DATA_TYPE_REAL = 7;
   public static final int DATA_TYPE_CHARACTER = 1;
   public static final int DATA_TYPE_CHARACTER_FOR_BIT_DATA = -2;
   public static final int DATA_TYPE_VARCHAR = 12;
   public static final int DATA_TYPE_VARCHAR_FOR_BIT_DATA = -3;
   public static final int DATA_TYPE_CLOB = 2005;
   public static final int DATA_TYPE_GRAPHIC = 1;
   public static final int DATA_TYPE_VARGRAPHIC = 12;
   public static final int DATA_TYPE_DBCLOB = 1111;
   public static final int DATA_TYPE_BINARY = -2;
   public static final int DATA_TYPE_VARBINARY = -3;
   public static final int DATA_TYPE_BLOB =2004;
   public static final int DATA_TYPE_DATE = 91;
   public static final int DATA_TYPE_TIME = 92;
   public static final int DATA_TYPE_TIMESTAMP = 93;
   public static final int DATA_TYPE_DATALINK = 70;
   public static final int DATA_TYPE_ROWID = 1111;
   public static final int DATA_TYPE_DISTINCT = 2001;
   public IColumntype getColumntypeInstance() {
       // TODO 自動生成されたメソッド・スタブ
       return null;
   }

   public int convDATA_TYPE(Column item) {
       return -1;
   }    
}

ColumnList?

package base;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class ColumnList extends Ary {
   //コネクション
   private ConnectionAutoClose _con;
   
   //コンストラクター
   public ColumnList(ConnectionAutoClose con,String tablename) throws SQLException{
       _con = con;
       // ステートメントオブジェクトを生成
       DatabaseMetaData dmd = _con.getMetaData();
       ResultSet rs = null;
       rs = dmd.getColumns(null, null,tablename,null);
       try {
           while(rs.next()){
               //テーブルのアイテムを追加します。
               Column column = null;
               
               if (_con instanceof ConnectionSQLite){
                   column = new ColumnSQLite();
               } else if (_con instanceof ConnectionODBC){
                   column = new ColumnDB2ODBC();
               }
               column.setByRs(rs);
               
               add(column);
           }
       } catch (Exception e) {
           e.printStackTrace();
       } finally {
           rs.close();
       }
   }
   
   
   //アイテムを返します。
   public Column getItem(int index){
       return (Column)get(index);
   }
   
   
   //テーブル名一覧を返します。
   public ArrayList getColumnNameList(){
       ArrayList ret = new ArrayList();
       for (int i = 0; i < size(); i++) {
           ret.add(getItem(i).getCOLUMN_NAME());
           
       }
       return ret;
   }


   /**
    * DDLを生成します。
    * @return 
    */
   public String makeDDL() {
       StringBuffer ret = new StringBuffer();
       Column column;
       for (int i = 0; i < size(); i++) {
           column = (Column) get(i);
           ret.append(column.makeDDL());
           ret.append(",");
       }
       return ret.toString();
   }
   
   public void printinfo(){
       for (int i = 0; i < size(); i++) {
           System.out.println("----------");
           getItem(i).printinfo();
       }
   }


   public void addItem(Column newColumn) {
       add(newColumn);
   }
}

ColumnMdbODBC

package base;

public class ColumnMdbODBC extends Column {
   public static final int DATA_TYPE_BIGINT = -5;
   public static final int DATA_TYPE_INTEGER = 4;
   public static final int DATA_TYPE_SMALLINT = 5;
   public static final int DATA_TYPE_DECIMAL = 3;
   public static final int DATA_TYPE_NUMERIC = 2;
   public static final int DATA_TYPE_DOUBLE_PRECISION = 8;
   public static final int DATA_TYPE_REAL = 7;
   public static final int DATA_TYPE_CHARACTER = 1;
   public static final int DATA_TYPE_CHARACTER_FOR_BIT_DATA = -2;
   public static final int DATA_TYPE_VARCHAR = 12;
   public static final int DATA_TYPE_VARCHAR_FOR_BIT_DATA = -3;
   public static final int DATA_TYPE_CLOB = 2005;
   public static final int DATA_TYPE_GRAPHIC = 1;
   public static final int DATA_TYPE_VARGRAPHIC = 12;
   public static final int DATA_TYPE_DBCLOB = 1111;
   public static final int DATA_TYPE_BINARY = -2;
   public static final int DATA_TYPE_VARBINARY = -3;
   public static final int DATA_TYPE_BLOB =2004;
   public static final int DATA_TYPE_DATE = 91;
   public static final int DATA_TYPE_TIME = 92;
   public static final int DATA_TYPE_TIMESTAMP = 93;
   public static final int DATA_TYPE_DATALINK = 70;
   public static final int DATA_TYPE_ROWID = 1111;
   public static final int DATA_TYPE_DISTINCT = 2001;

   public IColumntype getColumntypeInstance() {
       // TODO 自動生成されたメソッド・スタブ
       return null;
   }

   public int convDATA_TYPE(Column item) {
       return -1;
   }
}

ColumnSQLite

package base;

public class ColumnSQLite extends Column {
   public static final int DATA_TYPE_INTEGER = 1;
   public static final int DATA_TYPE_VARCHAR = 3;
   public static final int DATA_TYPE_BLOB = 4;
   public static final int DATA_TYPE_NULL = 5;
   public static final int DATA_TYPE_FLOAT = 2;

   public IColumntype getColumntypeInstance() {
       IColumntype ret = null;
       if (getDATA_TYPE() == DATA_TYPE_VARCHAR){
           ret = new VarcharColum();
       } else if (getDATA_TYPE() == DATA_TYPE_INTEGER){
           ret = new IntegerColumn();
       } else if (getDATA_TYPE() == DATA_TYPE_BLOB){
           ret = new IntegerColumn();
       } else if (getDATA_TYPE() == DATA_TYPE_FLOAT){
           ret = new IntegerColumn();
       }  else {
           ret = new NullColumn();
       }
       return ret;
   }
   
   //DBがサポートするデータタイプのクラスを返します。
   class IntegerColumn implements IColumntype{
       public String getDDL() {
           String ret = "INTEGER";
           if ("0".equals(getIS_NULLABLE())){
               ret = ret + getIS_NULLABLE();
           }
           return ret;
       }
   }
   
   class VarcharColum implements IColumntype{
       public String getDDL() {
           String ret = "VARCHAR(" + getCOLUMN_SIZE() +")";
           return ret;
       }
   }
   
   class BlobColumn implements IColumntype{
       public String getDDL() {
           String ret = "BLOB";
           return ret;
       }
   }
   
   class NullColumn implements IColumntype{
       public String getDDL() {
           return "";
       }
   }
   
   class FloatColumn implements IColumntype{
       public String getDDL() {
           String ret = "FLOAT";
           return ret;
       }
   }
   public int convDATA_TYPE(Column item) {
       if (item instanceof ColumnMdbODBC){
           switch (item.getDATA_TYPE()){
               case ColumnMdbODBC.DATA_TYPE_BIGINT:
                   return DATA_TYPE_INTEGER;
               default:
                   System.out.println("未定義:item.getDATA_TYPE()"+item.getDATA_TYPE());
                   return -1;
           }
       } else {
           System.out.println("未定義:item"+item.getClass().getName());
           return -1;
       }
   }
}

ColumnUtil?

package base;

import java.sql.ResultSet?; import java.sql.SQLException;

/**<P>

* 検索結果に格納されたカラム情報の結果をキーからJavaのシンボルに切り替える
* </P>
*/

public class ColumnUtil? {

   //TABLE_CAT
   public static String getTABLE_CAT(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getString("TABLE_CAT");
       } catch (Exception e) {
           return "";
       }
   }
   
   //TABLE_SCHEM
   public static String getTABLE_SCHEM(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getString("TABLE_SCHEM");
       } catch (Exception e) {
           return "";
       }
   }
   
   //TABLE_NAME
   public static String getTABLE_NAME(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getString("TABLE_NAME");
       } catch (Exception e) {
           return "";
       }
   }
   
   //COLUMN_NAME
   public static String getCOLUMN_NAME(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getString("COLUMN_NAME");
       } catch (Exception e) {
           return "";
       }        
   }
   
   //DATA_TYPE
   public static int getDATA_TYPE(ResultSet rsColumns) throws SQLException{
       return rsColumns.getInt("DATA_TYPE");
   }
   
   //TYPE_NAME
   public static String getTYPE_NAME(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getString("TYPE_NAME");
       } catch (Exception e) {
           return "";
       }        
   }
   
   //COLUMN_SIZE
   public static int getCOLUMN_SIZE(ResultSet rsColumns) throws SQLException{
       return rsColumns.getInt("COLUMN_SIZE");
   }
     
   //BUFFER_LENGTH
   public static int getBUFFER_LENGTH(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getInt("BUFFER_LENGTH");
       } catch (SQLException e) {
           return -1;
       }
   }
   
   //DECIMAL_DIGITS
   public static int getDECIMAL_DIGITS(ResultSet rsColumns) throws SQLException{
       return rsColumns.getInt("DECIMAL_DIGITS");
   }
   
   //NUM_PREC_RADIX
   public static int getNUM_PREC_RADIX(ResultSet rsColumns) throws SQLException{
       return rsColumns.getInt("NUM_PREC_RADIX");
   }
   
   //NULLABLE
   public static int getNULLABLE(ResultSet rsColumns) throws SQLException{
       return rsColumns.getInt("NULLABLE");
   }
   
   //REMARKS
   public static String getREMARKS(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getString("REMARKS");
       } catch (Exception e) {
           return "";
       }        
   }
   
   //COLUMN_DEF
   public static String getCOLUMN_DEF(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getString("COLUMN_DEF");
       } catch (Exception e) {
           return "";
       }        
   }
   
   //SQL_DATA_TYPE
   public static int getSQL_DATA_TYPE(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getInt("SQL_DATA_TYPE");
       } catch (Exception e) {
           return -1;
       }
   }
   
   //SQL_DATETIME_SUB
   public static int getSQL_DATETIME_SUB(ResultSet rsColumns) throws SQLException{
       return rsColumns.getInt("SQL_DATETIME_SUB");
   }
   
   //CHAR_OCTET_LENGTH
   public static int getCHAR_OCTET_LENGTH(ResultSet rsColumns) throws SQLException{
       return rsColumns.getInt("CHAR_OCTET_LENGTH");
   }
   
   //ORDINAL_POSITION
   public static int getORDINAL_POSITION(ResultSet rsColumns) throws SQLException{
       return rsColumns.getInt("ORDINAL_POSITION");
   }
   
   //IS_NULLABLE
   public static String getIS_NULLABLE(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getString("IS_NULLABLE");
       } catch (Exception e) {
           return "";
       }        
   }
   
   //JDBC_DATA_TYPE
   public static int getJDBC_DATA_TYPE(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getInt("JDBC_DATA_TYPE");
       } catch (Exception e) {
           return -1;
       }
   }
   
   //PSEUDO_COLUMN
   public static int getPSEUDO_COLUMN(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getInt("PSEUDO_COLUMN");
       } catch (Exception e) {
           return -1;
       }
   }
   
   //COLUMN_TEXT
   public static String getCOLUMN_TEXT(ResultSet rsColumns) throws SQLException{
       try {
           return rsColumns.getString("COLUMN_TEXT");
       } catch (Exception e) {
           return "";
       }
   }
   
}

ConnectionAutoClose?

package base;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.Map;

abstract public class ConnectionAutoClose implements java.sql.Connection{
    
   // ドライバクラスをロード
   public  static Class odbc = null;
   
   public Connection connection = null;
   
   //自動クローズ
   protected void finalize() {
       try {
           if (!isClosed()){
               close();
           }
       } catch (SQLException e) {
       }
   }
    
   public void clearWarnings() throws SQLException {
       connection.clearWarnings();
   }

   public void close() throws SQLException {
       connection.close();
   }

   public void commit() throws SQLException {
       connection.commit();
   }

   public Statement createStatement() throws SQLException {
       return connection.createStatement();
   }

   public Statement createStatement(int arg0, int arg1, int arg2) throws SQLException {
       return connection.createStatement(arg0, arg1, arg2);
   }

   public Statement createStatement(int arg0, int arg1) throws SQLException {
       return connection.createStatement(arg0, arg1);
   }

   public boolean getAutoCommit() throws SQLException {
       return connection.getAutoCommit();
   }

   public String getCatalog() throws SQLException {
       return connection.getCatalog();
   }

   public int getHoldability() throws SQLException {
       return connection.getHoldability();
   }

   public DatabaseMetaData getMetaData() throws SQLException {
       return connection.getMetaData();
   }

   public int getTransactionIsolation() throws SQLException {
       return connection.getTransactionIsolation();
   }

   public Map getTypeMap() throws SQLException {
       return connection.getTypeMap();
   }

   public SQLWarning getWarnings() throws SQLException {
       return connection.getWarnings();
   }

   public boolean isClosed() throws SQLException {
       return connection.isClosed();
   }

   public boolean isReadOnly() throws SQLException {
       return connection.isReadOnly();
   }

   public String nativeSQL(String arg0) throws SQLException {
       return connection.nativeSQL(arg0);
   }

   public CallableStatement prepareCall(String arg0, int arg1, int arg2, int arg3) throws SQLException {
       return connection.prepareCall(arg0, arg1, arg2, arg3);
   }

   public CallableStatement prepareCall(String arg0, int arg1, int arg2) throws SQLException {
       return connection.prepareCall(arg0, arg1, arg2);
   }

   public CallableStatement prepareCall(String arg0) throws SQLException {
       return connection.prepareCall(arg0);
   }

   public PreparedStatement prepareStatement(String arg0, int arg1, int arg2, int arg3) throws SQLException {
       return connection.prepareStatement(arg0, arg1, arg2, arg3);
   }

   public PreparedStatement prepareStatement(String arg0, int arg1, int arg2) throws SQLException {
       return connection.prepareStatement(arg0, arg1, arg2);
   }

   public PreparedStatement prepareStatement(String arg0, int arg1) throws SQLException {
       return connection.prepareStatement(arg0, arg1);
   }

   public PreparedStatement prepareStatement(String arg0, int[] arg1) throws SQLException {
       return connection.prepareStatement(arg0, arg1);
   }

   public PreparedStatement prepareStatement(String arg0, String[] arg1) throws SQLException {
       return connection.prepareStatement(arg0, arg1);
   }

   public PreparedStatement prepareStatement(String arg0) throws SQLException {
       return connection.prepareStatement(arg0);
   }

   public void releaseSavepoint(Savepoint arg0) throws SQLException {
       connection.releaseSavepoint(arg0);
   }

   public void rollback() throws SQLException {
       connection.rollback();
   }

   public void rollback(Savepoint arg0) throws SQLException {
       connection.rollback(arg0);
   }

   public void setAutoCommit(boolean arg0) throws SQLException {
       connection.setAutoCommit(arg0);
   }

   public void setCatalog(String arg0) throws SQLException {
       connection.setCatalog(arg0);
   } 

   public void setHoldability(int arg0) throws SQLException {
       connection.setHoldability(arg0);
   }

   public void setReadOnly(boolean arg0) throws SQLException {
       connection.setReadOnly(arg0);
   }

   public Savepoint setSavepoint() throws SQLException {
       return connection.setSavepoint();
   }

   public Savepoint setSavepoint(String arg0) throws SQLException {
       return connection.setSavepoint(arg0);
   }

   public void setTransactionIsolation(int arg0) throws SQLException {
       connection.setTransactionIsolation(arg0);
   }

   public void setTypeMap(Map arg0) throws SQLException {
       connection.setTypeMap(arg0);
   }

   public TableList getTableList() throws SQLException {
       return new TableList(this);
   }

   public void execute(String ddl) {
       
   }
   
}

ConnectionODBC

package base;

import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionODBC extends ConnectionAutoClose{
   
   //コンストラクタ
   public ConnectionODBC(String name) throws ClassNotFoundException, SQLException{
       super();
       if (odbc == null){
           odbc = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
           connection = DriverManager.getConnection("jdbc:odbc:" + name);
       }
   }
}

ConnectionSQLite

package base;

import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionSQLite extends ConnectionAutoClose{
   //コンストラクタ
   public ConnectionSQLite(String name) throws ClassNotFoundException, SQLException{
       super();
       if (odbc == null || connection == null){
           odbc = Class.forName("org.sqlite.JDBC");
           connection = DriverManager.getConnection("jdbc:sqlite:/" + name);
       }
   }
}

IColumntype

package base;

public interface IColumntype {
   public String getDDL();
}

IDataType?

package base;

public interface IDataType {
   public String makeDDL();
}

Table

package base;

import java.sql.SQLException;
import java.util.ArrayList;

public class Table {
   public String _name;
   public String _type;
   public ConnectionAutoClose _con;
   private ColumnList columnlist = null;
   
   public Table(ConnectionAutoClose con,String name, String type) {
       _name = name;
       _type = type;
       _con = con;
   }
   public ColumnList getColumnList() throws SQLException {
       if (columnlist == null){
           columnlist = new ColumnList(_con, _name);
       }
       
       return columnlist;
   }
   //テーブルを複製します。
   public void copy(ConnectionAutoClose con) throws SQLException {
       //conにテープル追加
       if (!con.getTableList().exist(_name)){
           Table newTable = new Table(con,_name,_type);
           con.getTableList().add(newTable);
           
           //カラムの追加
           ColumnList list = getColumnList();
           
           for (int i = 0; i < list.size(); i++) {
               Column newColumn = new ColumnSQLite();
               newColumn.setByColumn(list.getItem(i));
               newTable.getColumnList().addItem(newColumn);
           }
           
           String ddl = "CREATE TABLE " + _name + " (" +
           newTable.getColumnList().makeDDL() +
           //カラム
           "PRIMARY KEY(" + ")" +
           ")";
           
           con.execute(ddl);
       } else {
           System.out.println(_name + "はすでにあります。");
       }
   }
}

TableList?

package base; 

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class TableList extends Ary {
   //コネクション
   private ConnectionAutoClose _con;
   
   //コンストラクター
   public TableList(ConnectionAutoClose con) throws SQLException{
       _con = con;
       // ステートメントオブジェクトを生成
       //Statement stmt = _con.createStatement();
       DatabaseMetaData dmd = _con.getMetaData();
       String types[] = { "TABLE" };
       ResultSet rs = null;
       rs = dmd.getTables(null, null,"%", types);
       try {
           while(rs.next()){
               //テーブルのアイテムを追加します。
               Table table = new Table(_con,rs.getString("TABLE_NAME"),rs.getString("TABLE_TYPE"));
               add(table);
           }
       } finally {
           rs.close();
       }
   }
   
   //アイテムを返します。
   public Table getItem(int index){
       return (Table)get(index);
   }
   
   
   //テーブル名一覧を返します。
   public ArrayList getTableNameList(){
       ArrayList ret = new ArrayList();
       for (int i = 0; i < size(); i++) {
           ret.add(getItem(i)._name);
           
       }
       return ret;
   }
   public boolean exist(String _name) {
       String name;
       for (int i = 0; i < size(); i++) {
           if (getItem(i)._name.equals(_name)){
               return true;
           }
           
       }
       return false;
   }
   
   
}
トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2010-06-29 (火) 11:11:11 (4132d)