- 追加された行はこの色です。
- 削除された行はこの色です。
*目次 [#va77d7e8]
#contents
*本体ダウンロード [#u3d71059]
コマンドライン対応のWindows版を落としてくる。
DLLでもいいんだけどね。
解凍すると、
sqlite3.exe
というファイルがある。
ここでは、下記のフォルダを作成してまとめて格納することにする。
c:\sqlite\
**URL [#u6563f62]
http://www.sqlite.org/download.html
*空のデータベース作成 [#ue698128]
**Javaプログラムから [#gf845503]
存在しないDBにコネクションを張ろうとすると自動的に作成されます。
Class.forName("org.sqlite.JDBC");
Connection c = DriverManager.getConnection("jdbc:sqlite:/c:/sqlite/testdatabase3");
**コマンドプロンプトから [#y6c5b1ed]
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を入力する。 [#o7be3081]
create table testtable(
col1 int primary key,
col2 char(128),
col3 char(128)
);
*コマンド [#q8157cbb]
**テーブル一覧を見る。 [#yfc76f44]
.tables
**SQLite終了 [#wabd86b1]
.quit
*JDBCドライバインストール [#nb6b523b]
2箇所開発しているところがあるってことなのかな?でも、JDBCが動くサイトと、動かないサイトがあるんだよね。
**OKなJDBC [#f23f80d0]
http://www.zentus.com/sqlitejdbc/
ちゃんと動作したバージョンは下記のバージョン
sqlitejdbc-v056.jar
**NGなJDBC [#pa1de7e3]
下記のは使い物にならんかった。正式版っぽい顔してけしからん。
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サンプル [#i8ec443d]
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");
}
}
*メモリ上にテーブルを作成する場合 [#vc779f30]
Connection c = DriverManager.getConnection("jdbc:sqlite::memory:");
*編集ツール [#q02b78f1]
-TkSQLite
Windows と Linux で使える編集ツールです。
-PupSQLite
Windows で使える編集ツールです。
-SQLite Manager Firefox add-ons (英語)
Firefox で使える編集ツールです。
-Lita - SQLite Administration Tool (英語)
Adobe AIR で使える編集ツールです。
*SQLite が認識できる SQL [#sb731c7d]
http://net-newbie.com/sqlite/lang.html
*テストツール [#hb3d8d7e]
※書きかけです。
テストツールを作成する際、ある程度の決まりごとがあるので、それらのコードをまとめておく。
設計方針としては、極力ローカル変数で宣言しなくても、目的のコードを記述できるようにする。
**コード [#b1d83f4f]
作りかけではあるが、クラスのデザインの方向性を示せるレベルだとは思っています。
***Ary [#ba83ecd8]
package base;
import java.util.ArrayList;
public class Ary extends ArrayList {
}
***Column [#d7352d4c]
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 [#l70c25d8]
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 [#t5dae814]
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 [#ub0ee196]
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 [#g32591b6]
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 [#d7b85239]
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 [#g384ef38]
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 [#e0c73226]
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 [#e9407146]
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 [#ya08223f]
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 [#o30ac269]
package base;
public interface IColumntype {
public String getDDL();
}
***IDataType [#z851fa6b]
package base;
public interface IDataType {
public String makeDDL();
}
***Table [#bb08c5ca]
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 [#r5a7bff9]
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;
}
}