データベースアクセスを抽象化する(してないかも？)
戻る
::::::::::::::
AbstractDataBaseAccessor.java
::::::::::::::
import java.io.*;
import java.util.*;
import java.sql.*;
/**
$Id: database-access.html,v 1.1 2009/06/22 16:12:08 kishi Exp kishi $
データベースをアクセスするための抽象クラス
のはずだったが、全てのメソッドが実装済み（後でabstractメソッドが必要になるかもしれないのでとりあえずabstractなクラスにしておく)
MS-ACCESSのVARCHAR型はどっちみち５０バイトしか格納できないので本格版は別途対応
*/
abstract public class AbstractDataBaseAccessor {
/** XMLで記述されたプロパティファイル */
private final String PROP_XML = "../conf/prop.xml";
private Properties prop;
/** データベースへのコネクション(サブクラスでも使用するのでprotected修飾子にしておく) */
protected java.sql.Connection connection = null;
/** ステートメント */
protected java.sql.PreparedStatement statement;
public AbstractDataBaseAccessor() {
init();
}
// データベースの接続設定のメソッドを実装しておく
public void init() {
String JDBC_DRIVER = null;
String JDBC_URL = null;
String USERID = null;
String PASSWORD = null;
prop = new Properties();
try {
InputStream stream = new FileInputStream( PROP_XML );
prop.loadFromXML( stream );
stream.close();
JDBC_DRIVER = prop.getProperty( "JDBC_DRIVER" );
JDBC_URL = prop.getProperty( "JDBC_URL" );
USERID = prop.getProperty( "USERID" );
PASSWORD = prop.getProperty( "PASSWORD" );
} catch ( IOException e ) {
e.printStackTrace();
}
// コネクションを取得
try {
Class.forName( JDBC_DRIVER );
connection = DriverManager.getConnection( JDBC_URL, USERID, PASSWORD );
} catch ( Exception e ) {
e.printStackTrace();
}
//-------------------------------------------------------------------
// 設定ファイルを読み取りコネクションを取得するところまで実装する
//-------------------------------------------------------------------
}
public void dumpProperties() {
// 一覧を出力
prop.list( System.out );
}
public void setParam( int index, Object param ) throws Exception {
try {
statement.setObject( index, param );
} catch ( Exception e ) {
throw e;
}
}
/** VARCHAR型の対応 */
/*
public void setCharacterStream( int index, String str ) throws Exception {
try {
StringReader reader = new StringReader( str );
statement.setCharacterStream( index, reader, str.length() );
} catch ( Exception e ) {
throw e;
}
}
*/
public void setSQL( String sql ) throws Exception {
// SQLに引数を与えて実行する場合は、原則PreparedStatementを使うこと！→SQLインジェクション対応
try {
statement = connection.prepareStatement( sql );
} catch ( Exception e ) {
throw e;
}
}
/** DB照会 */
public List executeQuery() throws Exception {
List resultList = new LinkedList();
try {
ResultSet resultSet = statement.executeQuery();
ResultSetMetaData rsmd = resultSet.getMetaData();
int numCols = rsmd.getColumnCount();
// System.out.println( "カラム数＝" + numCols );
// 各カラムの型を取得する(ここはMSアクセスのための対応 -- 他のDBでは必要ないはずである)
String[] columnTypeNames = new String[ numCols ];
for ( int i = 0;i < numCols;i++ ) {
columnTypeNames[ i ] = rsmd.getColumnTypeName( i + 1 );
// System.out.println( columnTypeNames[i] );
}
while ( resultSet.next() ) {
Object[] cols = new Object[ numCols ];
for ( int i = 0;i < numCols;i++ ) {
if ( "VARCHAR".equals( columnTypeNames[ i ] ) ) {
//==========================================================================
// ちなみにLONGCHAR型の場合はgetObject()でデータ取得が可能のようである
//==========================================================================
BufferedReader reader = new BufferedReader( resultSet.getCharacterStream( i + 1 ) );
StringBuilder sb = new StringBuilder();
////////////////////////////////////////////////////////////
// int c;
// while ( ( c = reader.read() ) != -1 ) {
// sb.append( ( char ) c );
// }
////////////////////////////////////////////////////////////
String line = null;
while ( ( line = reader.readLine() ) != null ) {
sb.append( line + "\n" );
}
cols[ i ] = ( sb.toString() ).trim();
} else {
cols[ i ] = resultSet.getObject( i + 1 );
}
}
resultList.add( cols );
}
} catch ( Exception e ) {
throw e;
}
return resultList;
}
/** DB更新 */
public int executeUpdate() throws Exception {
int resultCode = 0;
try {
connection.setAutoCommit( false );
resultCode = statement.executeUpdate();
connection.commit();
} catch ( Exception e ) {
throw e;
}
return resultCode;
}
/** お掃除処理 */
public void cleanup() throws Exception {
try {
if ( statement != null ) {
statement.close();
statement = null;
}
if ( connection != null ) {
connection.close();
connection = null;
}
} catch ( Exception e ) {
throw e;
}
}
}
/*
$ javap sun.jdbc.odbc.JdbcOdbcPreparedStatement
Compiled from "JdbcOdbcPreparedStatement.java"
public class sun.jdbc.odbc.JdbcOdbcPreparedStatement extends sun.jdbc.odbc.JdbcOdbcStatement implements java.sql.PreparedStatement{
protected int numParams;
protected sun.jdbc.odbc.JdbcOdbcBoundParam[] boundParams;
protected sun.jdbc.odbc.JdbcOdbcBoundArrayOfParams arrayParams;
protected java.util.Vector batchSqlVec;
protected boolean batchSupport;
protected boolean batchParamsOn;
protected int batchSize;
protected int arrayDef;
protected int arrayScale;
protected int StringDef;
protected int NumberDef;
protected int NumberScale;
protected int batchRCFlag;
protected int[] paramsProcessed;
protected int[] paramStatusArray;
protected long[] pA1;
protected long[] pA2;
protected int binaryPrec;
protected sun.jdbc.odbc.JdbcOdbcUtils utils;
public sun.jdbc.odbc.JdbcOdbcPreparedStatement(sun.jdbc.odbc.JdbcOdbcConnectionInterface);
public void initialize(sun.jdbc.odbc.JdbcOdbc, long, long, java.util.Hashtable, int, int) throws java.sql.SQLException;
public java.sql.ResultSet executeQuery() throws java.sql.SQLException;
public java.sql.ResultSet executeQuery(java.lang.String) throws java.sql.SQLException;
public int executeUpdate() throws java.sql.SQLException;
public int executeUpdate(java.lang.String) throws java.sql.SQLException;
public boolean execute(java.lang.String) throws java.sql.SQLException;
public synchronized boolean execute() throws java.sql.SQLException;
public void setNull(int, int) throws java.sql.SQLException;
public void setBoolean(int, boolean) throws java.sql.SQLException;
public void setByte(int, byte) throws java.sql.SQLException;
public void setShort(int, short) throws java.sql.SQLException;
public void setInt(int, int) throws java.sql.SQLException;
public void setLong(int, long) throws java.sql.SQLException;
public void setReal(int, float) throws java.sql.SQLException;
public void setFloat(int, float) throws java.sql.SQLException;
public void setDouble(int, double) throws java.sql.SQLException;
public void setBigDecimal(int, java.math.BigDecimal) throws java.sql.SQLException;
public void setDecimal(int, java.math.BigDecimal) throws java.sql.SQLException;
public void setString(int, java.lang.String) throws java.sql.SQLException;
public void setBytes(int, byte[]) throws java.sql.SQLException;
public void setDate(int, java.sql.Date) throws java.sql.SQLException;
public void setTime(int, java.sql.Time) throws java.sql.SQLException;
public void setTimestamp(int, java.sql.Timestamp) throws java.sql.SQLException;
public void setAsciiStream(int, java.io.InputStream, int) throws java.sql.SQLException;
public void setUnicodeStream(int, java.io.InputStream, int) throws java.sql.SQLException;
public void setBinaryStream(int, java.io.InputStream, int) throws java.sql.SQLException;
public void clearParameters() throws java.sql.SQLException;
public void clearParameter(int) throws java.sql.SQLException;
public void setObject(int, java.lang.Object) throws java.sql.SQLException;
public void setObject(int, java.lang.Object, int) throws java.sql.SQLException;
public void setObject(int, java.lang.Object, int, int) throws java.sql.SQLException;
public void addBatch(java.lang.String) throws java.sql.SQLException;
public void clearBatch();
public void addBatch() throws java.sql.SQLException;
public int[] executeBatchUpdate() throws java.sql.BatchUpdateException;
protected int[] executeNoParametersBatch() throws java.sql.BatchUpdateException;
protected int getStmtParameterAttr(int) throws java.sql.SQLException;
protected void setStmtParameterSize(int) throws java.sql.SQLException;
protected void bindArrayOfParameters(int, int, int, int, java.lang.Object[], int[]) throws java.sql.SQLException;
protected int[] emulateExecuteBatch() throws java.sql.BatchUpdateException;
protected void cleanUpBatch();
protected void setPrecisionScaleArgs(java.lang.Object[], int[]);
protected void setSqlType(int, int);
protected int getSqlType(int);
public void setCharacterStream(int, java.io.Reader, int) throws java.sql.SQLException;
public void setRef(int, java.sql.Ref) throws java.sql.SQLException;
public void setBlob(int, java.sql.Blob) throws java.sql.SQLException;
public void setClob(int, java.sql.Clob) throws java.sql.SQLException;
public void setArray(int, java.sql.Array) throws java.sql.SQLException;
public java.sql.ResultSetMetaData getMetaData() throws java.sql.SQLException;
public void setDate(int, java.sql.Date, java.util.Calendar) throws java.sql.SQLException;
public void setTime(int, java.sql.Time, java.util.Calendar) throws java.sql.SQLException;
public void setTimestamp(int, java.sql.Timestamp, java.util.Calendar) throws java.sql.SQLException;
public void setNull(int, int, java.lang.String) throws java.sql.SQLException;
public void initBoundParam() throws java.sql.SQLException;
protected byte[] allocBindBuf(int, int);
protected byte[] getDataBuf(int);
protected byte[] getLengthBuf(int);
public int getParamLength(int);
protected void putParamData(int) throws java.sql.SQLException, sun.jdbc.odbc.JdbcOdbcSQLWarning;
public void setStream(int, java.io.InputStream, int, int, int) throws java.sql.SQLException;
protected void setChar(int, int, int, java.lang.String) throws java.sql.SQLException;
protected void setBinary(int, int, byte[]) throws java.sql.SQLException;
protected int getTypeFromObjectArray(java.lang.Object[]);
public synchronized void close() throws java.sql.SQLException;
public synchronized void FreeIntParams();
public synchronized void FreeParams() throws java.lang.NullPointerException;
public void setSql(java.lang.String);
public java.lang.Object[] getObjects();
public int[] getObjectTypes();
public int getParamCount();
protected void setInputParameter(int, boolean);
public void setURL(int, java.net.URL) throws java.sql.SQLException;
public java.sql.ParameterMetaData getParameterMetaData() throws java.sql.SQLException;
}
*/
::::::::::::::
DBAccessTest1.java
::::::::::::::
import java.sql.*;
import java.util.*;
import java.io.*;
/*
* $Id: database-access.html,v 1.1 2009/06/22 16:12:08 kishi Exp kishi $
* JDBC⇒ODBC経由でMSACCESSにアクセスしてみる
*/
public class DBAccessTest1 {
static private String url = "jdbc:odbc:yacms"; // yacmsはマシン内のシステムDSNです
static private String query = "select * from node";
public DBAccessTest1() {
try {
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
} catch ( Exception e ) {
e.printStackTrace();
}
try {
Connection conn = DriverManager.getConnection( url, "admin", "admin" );
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery( query );
while ( rs.next() ) {
int nodeId = rs.getInt( "nodeId" );
Object isCategory = rs.getObject( "isCategory" );
int parentNodeId = rs.getInt( "parentNodeId" );
// MS-ACCESSの場合は、文字列型のフィールドは、以下のようにして取得する(使えんなマジで・・・）
Reader title = rs.getCharacterStream( "title" );
System.out.printf( "%3d ", nodeId );
System.out.printf( "%-5s ", isCategory.toString() );
System.out.printf( "%3d ", parentNodeId );
int c;
while ( ( c = title.read() ) != -1 ) {
System.out.print( ( char ) c );
}
System.out.println();
}
stmt.close();
conn.close();
} catch ( Exception e ) {
e.printStackTrace();
}
}
static public void main( String args[] ) {
DBAccessTest1 test1 = new DBAccessTest1();
}
}
/*
$ javap java.sql.ResultSet
Compiled from "ResultSet.java"
public interface java.sql.ResultSet{
public static final int FETCH_FORWARD;
public static final int FETCH_REVERSE;
public static final int FETCH_UNKNOWN;
public static final int TYPE_FORWARD_ONLY;
public static final int TYPE_SCROLL_INSENSITIVE;
public static final int TYPE_SCROLL_SENSITIVE;
public static final int CONCUR_READ_ONLY;
public static final int CONCUR_UPDATABLE;
public static final int HOLD_CURSORS_OVER_COMMIT;
public static final int CLOSE_CURSORS_AT_COMMIT;
public abstract boolean next() throws java.sql.SQLException;
public abstract void close() throws java.sql.SQLException;
public abstract boolean wasNull() throws java.sql.SQLException;
public abstract java.lang.String getString(int) throws java.sql.SQLException;
public abstract boolean getBoolean(int) throws java.sql.SQLException;
public abstract byte getByte(int) throws java.sql.SQLException;
public abstract short getShort(int) throws java.sql.SQLException;
public abstract int getInt(int) throws java.sql.SQLException;
public abstract long getLong(int) throws java.sql.SQLException;
public abstract float getFloat(int) throws java.sql.SQLException;
public abstract double getDouble(int) throws java.sql.SQLException;
public abstract java.math.BigDecimal getBigDecimal(int, int) throws java.sql.SQLException;
public abstract byte[] getBytes(int) throws java.sql.SQLException;
public abstract java.sql.Date getDate(int) throws java.sql.SQLException;
public abstract java.sql.Time getTime(int) throws java.sql.SQLException;
public abstract java.sql.Timestamp getTimestamp(int) throws java.sql.SQLException;
public abstract java.io.InputStream getAsciiStream(int) throws java.sql.SQLException;
public abstract java.io.InputStream getUnicodeStream(int) throws java.sql.SQLException;
public abstract java.io.InputStream getBinaryStream(int) throws java.sql.SQLException;
public abstract java.lang.String getString(java.lang.String) throws java.sql.SQLException;
public abstract boolean getBoolean(java.lang.String) throws java.sql.SQLException;
public abstract byte getByte(java.lang.String) throws java.sql.SQLException;
public abstract short getShort(java.lang.String) throws java.sql.SQLException;
public abstract int getInt(java.lang.String) throws java.sql.SQLException;
public abstract long getLong(java.lang.String) throws java.sql.SQLException;
public abstract float getFloat(java.lang.String) throws java.sql.SQLException;
public abstract double getDouble(java.lang.String) throws java.sql.SQLException;
public abstract java.math.BigDecimal getBigDecimal(java.lang.String, int) throws java.sql.SQLException;
public abstract byte[] getBytes(java.lang.String) throws java.sql.SQLException;
public abstract java.sql.Date getDate(java.lang.String) throws java.sql.SQLException;
public abstract java.sql.Time getTime(java.lang.String) throws java.sql.SQLException;
public abstract java.sql.Timestamp getTimestamp(java.lang.String) throws java.sql.SQLException;
public abstract java.io.InputStream getAsciiStream(java.lang.String) throws java.sql.SQLException;
public abstract java.io.InputStream getUnicodeStream(java.lang.String) throws java.sql.SQLException;
public abstract java.io.InputStream getBinaryStream(java.lang.String) throws java.sql.SQLException;
public abstract java.sql.SQLWarning getWarnings() throws java.sql.SQLException;
public abstract void clearWarnings() throws java.sql.SQLException;
public abstract java.lang.String getCursorName() throws java.sql.SQLException;
public abstract java.sql.ResultSetMetaData getMetaData() throws java.sql.SQLException;
public abstract java.lang.Object getObject(int) throws java.sql.SQLException;
public abstract java.lang.Object getObject(java.lang.String) throws java.sql.SQLException;
public abstract int findColumn(java.lang.String) throws java.sql.SQLException;
public abstract java.io.Reader getCharacterStream(int) throws java.sql.SQLException;
public abstract java.io.Reader getCharacterStream(java.lang.String) throws java.sql.SQLException;
public abstract java.math.BigDecimal getBigDecimal(int) throws java.sql.SQLException;
public abstract java.math.BigDecimal getBigDecimal(java.lang.String) throws java.sql.SQLException;
public abstract boolean isBeforeFirst() throws java.sql.SQLException;
public abstract boolean isAfterLast() throws java.sql.SQLException;
public abstract boolean isFirst() throws java.sql.SQLException;
public abstract boolean isLast() throws java.sql.SQLException;
public abstract void beforeFirst() throws java.sql.SQLException;
public abstract void afterLast() throws java.sql.SQLException;
public abstract boolean first() throws java.sql.SQLException;
public abstract boolean last() throws java.sql.SQLException;
public abstract int getRow() throws java.sql.SQLException;
public abstract boolean absolute(int) throws java.sql.SQLException;
public abstract boolean relative(int) throws java.sql.SQLException;
public abstract boolean previous() throws java.sql.SQLException;
public abstract void setFetchDirection(int) throws java.sql.SQLException;
public abstract int getFetchDirection() throws java.sql.SQLException;
public abstract void setFetchSize(int) throws java.sql.SQLException;
public abstract int getFetchSize() throws java.sql.SQLException;
public abstract int getType() throws java.sql.SQLException;
public abstract int getConcurrency() throws java.sql.SQLException;
public abstract boolean rowUpdated() throws java.sql.SQLException;
public abstract boolean rowInserted() throws java.sql.SQLException;
public abstract boolean rowDeleted() throws java.sql.SQLException;
public abstract void updateNull(int) throws java.sql.SQLException;
public abstract void updateBoolean(int, boolean) throws java.sql.SQLException;
public abstract void updateByte(int, byte) throws java.sql.SQLException;
public abstract void updateShort(int, short) throws java.sql.SQLException;
public abstract void updateInt(int, int) throws java.sql.SQLException;
public abstract void updateLong(int, long) throws java.sql.SQLException;
public abstract void updateFloat(int, float) throws java.sql.SQLException;
public abstract void updateDouble(int, double) throws java.sql.SQLException;
public abstract void updateBigDecimal(int, java.math.BigDecimal) throws java.sql.SQLException;
public abstract void updateString(int, java.lang.String) throws java.sql.SQLException;
public abstract void updateBytes(int, byte[]) throws java.sql.SQLException;
public abstract void updateDate(int, java.sql.Date) throws java.sql.SQLException;
public abstract void updateTime(int, java.sql.Time) throws java.sql.SQLException;
public abstract void updateTimestamp(int, java.sql.Timestamp) throws java.sql.SQLException;
public abstract void updateAsciiStream(int, java.io.InputStream, int) throws java.sql.SQLException;
public abstract void updateBinaryStream(int, java.io.InputStream, int) throws java.sql.SQLException;
public abstract void updateCharacterStream(int, java.io.Reader, int) throws java.sql.SQLException;
public abstract void updateObject(int, java.lang.Object, int) throws java.sql.SQLException;
public abstract void updateObject(int, java.lang.Object) throws java.sql.SQLException;
public abstract void updateNull(java.lang.String) throws java.sql.SQLException;
public abstract void updateBoolean(java.lang.String, boolean) throws java.sql.SQLException;
public abstract void updateByte(java.lang.String, byte) throws java.sql.SQLException;
public abstract void updateShort(java.lang.String, short) throws java.sql.SQLException;
public abstract void updateInt(java.lang.String, int) throws java.sql.SQLException;
public abstract void updateLong(java.lang.String, long) throws java.sql.SQLException;
public abstract void updateFloat(java.lang.String, float) throws java.sql.SQLException;
public abstract void updateDouble(java.lang.String, double) throws java.sql.SQLException;
public abstract void updateBigDecimal(java.lang.String, java.math.BigDecimal) throws java.sql.SQLException;
public abstract void updateString(java.lang.String, java.lang.String) throws java.sql.SQLException;
public abstract void updateBytes(java.lang.String, byte[]) throws java.sql.SQLException;
public abstract void updateDate(java.lang.String, java.sql.Date) throws java.sql.SQLException;
public abstract void updateTime(java.lang.String, java.sql.Time) throws java.sql.SQLException;
public abstract void updateTimestamp(java.lang.String, java.sql.Timestamp) throws java.sql.SQLException;
public abstract void updateAsciiStream(java.lang.String, java.io.InputStream, int) throws java.sql.SQLException;
public abstract void updateBinaryStream(java.lang.String, java.io.InputStream, int) throws java.sql.SQLException;
public abstract void updateCharacterStream(java.lang.String, java.io.Reader, int) throws java.sql.SQLException;
public abstract void updateObject(java.lang.String, java.lang.Object, int) throws java.sql.SQLException;
public abstract void updateObject(java.lang.String, java.lang.Object) throws java.sql.SQLException;
public abstract void insertRow() throws java.sql.SQLException;
public abstract void updateRow() throws java.sql.SQLException;
public abstract void deleteRow() throws java.sql.SQLException;
public abstract void refreshRow() throws java.sql.SQLException;
public abstract void cancelRowUpdates() throws java.sql.SQLException;
public abstract void moveToInsertRow() throws java.sql.SQLException;
public abstract void moveToCurrentRow() throws java.sql.SQLException;
public abstract java.sql.Statement getStatement() throws java.sql.SQLException;
public abstract java.lang.Object getObject(int, java.util.Map) throws java.sql.SQLException;
public abstract java.sql.Ref getRef(int) throws java.sql.SQLException;
public abstract java.sql.Blob getBlob(int) throws java.sql.SQLException;
public abstract java.sql.Clob getClob(int) throws java.sql.SQLException;
public abstract java.sql.Array getArray(int) throws java.sql.SQLException;
public abstract java.lang.Object getObject(java.lang.String, java.util.Map) throws java.sql.SQLException;
public abstract java.sql.Ref getRef(java.lang.String) throws java.sql.SQLException;
public abstract java.sql.Blob getBlob(java.lang.String) throws java.sql.SQLException;
public abstract java.sql.Clob getClob(java.lang.String) throws java.sql.SQLException;
public abstract java.sql.Array getArray(java.lang.String) throws java.sql.SQLException;
public abstract java.sql.Date getDate(int, java.util.Calendar) throws java.sql.SQLException;
public abstract java.sql.Date getDate(java.lang.String, java.util.Calendar) throws java.sql.SQLException;
public abstract java.sql.Time getTime(int, java.util.Calendar) throws java.sql.SQLException;
public abstract java.sql.Time getTime(java.lang.String, java.util.Calendar) throws java.sql.SQLException;
public abstract java.sql.Timestamp getTimestamp(int, java.util.Calendar) throws java.sql.SQLException;
public abstract java.sql.Timestamp getTimestamp(java.lang.String, java.util.Calendar) throws java.sql.SQLException;
public abstract java.net.URL getURL(int) throws java.sql.SQLException;
public abstract java.net.URL getURL(java.lang.String) throws java.sql.SQLException;
public abstract void updateRef(int, java.sql.Ref) throws java.sql.SQLException;
public abstract void updateRef(java.lang.String, java.sql.Ref) throws java.sql.SQLException;
public abstract void updateBlob(int, java.sql.Blob) throws java.sql.SQLException;
public abstract void updateBlob(java.lang.String, java.sql.Blob) throws java.sql.SQLException;
public abstract void updateClob(int, java.sql.Clob) throws java.sql.SQLException;
public abstract void updateClob(java.lang.String, java.sql.Clob) throws java.sql.SQLException;
public abstract void updateArray(int, java.sql.Array) throws java.sql.SQLException;
public abstract void updateArray(java.lang.String, java.sql.Array) throws java.sql.SQLException;
}
*/
::::::::::::::
DBAccessTest2.java
::::::::::::::
import java.sql.*;
import java.util.*;
/*
* $Id: database-access.html,v 1.1 2009/06/22 16:12:08 kishi Exp kishi $
* JDBC⇒ODBC経由でMSACCESSにアクセスしてみる
*/
public class DBAccessTest2 {
static private String url = "jdbc:odbc:yacms"; // yacmsはマシン内のシステムDSNです
public DBAccessTest2() {
try {
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
} catch ( Exception e ) {
e.printStackTrace();
}
try {
Connection conn = DriverManager.getConnection( url, "admin", "admin" );
// SQLに引数を与えて実行する場合は、原則PreparedStatementを使うこと！→SQLインジェクション対応
java.sql.PreparedStatement stmt = conn.prepareStatement(
"SELECT * FROM node WHERE nodeId > ? " );
stmt.setInt( 1, 2 );
java.sql.ResultSet rs = stmt.executeQuery();
while ( rs.next() ) {
int id = rs.getInt( "nodeId" );
boolean isCategory = rs.getBoolean( "isCategory" );
String name = rs.getString( "title" );
System.out.println( id + "\t"
+ isCategory + "\t"
+ name );
}
stmt.close();
conn.close();
} catch ( Exception e ) {
e.printStackTrace();
}
}
static public void main( String args[] ) {
DBAccessTest2 test2 = new DBAccessTest2();
}
}
::::::::::::::
DBUpdateTest1.java
::::::::::::::
::::::::::::::
TableDescriptionRetriever.java
::::::::::::::
import java.sql.*;
import java.util.*;
/*
* $Id: database-access.html,v 1.1 2009/06/22 16:12:08 kishi Exp kishi $
* JDBC⇒ODBC経由でMSACCESSにアクセスしてみる
*/
public class TableDescriptionRetriever {
static private String url = "jdbc:odbc:yacms"; // yacmsはマシン内のシステムDSNです
public TableDescriptionRetriever( String tableName ) {
try {
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
} catch ( Exception e ) {
e.printStackTrace();
}
try {
Connection conn = DriverManager.getConnection( url, "admin", "admin" );
String query = "select * from " + tableName;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery( query );
// メタデータの取得
ResultSetMetaData rsmd = rs.getMetaData();
int numCols = rsmd.getColumnCount();
for ( int i = 1; i <= numCols; i++ ) { // なぜか１から始まる
String columnName = rsmd.getColumnName( i );
String columnTypeName = rsmd.getColumnTypeName( i );
int precision = rsmd.getPrecision( i );
int scale = rsmd.getScale( i );
System.out.printf( "%3d: %-20s %-20s %3d %10d\n",
i , columnName, columnTypeName, scale, precision );
}
stmt.close();
conn.close();
} catch ( Exception e ) {
e.printStackTrace();
}
}
static public void main( String args[] ) {
if ( args.length != 1 ) {
System.err.println( "Usage: java TableDescriptionRetriever [tablename]" );
System.exit( 1 );
}
TableDescriptionRetriever retriever = new TableDescriptionRetriever( args[ 0 ] );
}
}
/*
$ javap java.sql.ResultSetMetaData
Compiled from "ResultSetMetaData.java"
public interface java.sql.ResultSetMetaData{
public static final int columnNoNulls;
public static final int columnNullable;
public static final int columnNullableUnknown;
public abstract int getColumnCount() throws java.sql.SQLException;
public abstract boolean isAutoIncrement(int) throws java.sql.SQLException;
public abstract boolean isCaseSensitive(int) throws java.sql.SQLException;
public abstract boolean isSearchable(int) throws java.sql.SQLException;
public abstract boolean isCurrency(int) throws java.sql.SQLException;
public abstract int isNullable(int) throws java.sql.SQLException;
public abstract boolean isSigned(int) throws java.sql.SQLException;
public abstract int getColumnDisplaySize(int) throws java.sql.SQLException;
public abstract java.lang.String getColumnLabel(int) throws java.sql.SQLException;
public abstract java.lang.String getColumnName(int) throws java.sql.SQLException;
public abstract java.lang.String getSchemaName(int) throws java.sql.SQLException;
public abstract int getPrecision(int) throws java.sql.SQLException;
public abstract int getScale(int) throws java.sql.SQLException;
public abstract java.lang.String getTableName(int) throws java.sql.SQLException;
public abstract java.lang.String getCatalogName(int) throws java.sql.SQLException;
public abstract int getColumnType(int) throws java.sql.SQLException;
public abstract java.lang.String getColumnTypeName(int) throws java.sql.SQLException;
public abstract boolean isReadOnly(int) throws java.sql.SQLException;
public abstract boolean isWritable(int) throws java.sql.SQLException;
public abstract boolean isDefinitelyWritable(int) throws java.sql.SQLException;
public abstract java.lang.String getColumnClassName(int) throws java.sql.SQLException;
}
*/
::::::::::::::
Test1.java
::::::::::::::
import java.util.*;
import java.sql.*;
/**
$Id: database-access.html,v 1.1 2009/06/22 16:12:08 kishi Exp kishi $
DB照会用のテストコード
*/
public class Test1 extends AbstractDataBaseAccessor {
public Test1() {
super();
}
static public void main( String[] args ) {
Test1 test1 = new Test1();
test1.dumpProperties();
try {
test1.setSQL( "SELECT nodeId, isCategory, title FROM node WHERE nodeId > ? " );
test1.setParam( 1, new Integer( 2 ) );
List list = test1.executeQuery();
Iterator iterator = list.iterator();
while ( iterator.hasNext() ) {
Object[] record = ( Object[] ) iterator.next();
for ( int i = 0;i < record.length;i++ ) {
if ( record[ i ] != null ) {
System.out.print( record[ i ].toString() );
} else {
System.out.print( "NULL" );
}
System.out.print( "\t" );
}
System.out.println();
}
test1.cleanup();
} catch ( Exception e ) {
e.printStackTrace();
}
}
}
::::::::::::::
Test2.java
::::::::::::::
import java.util.*;
import java.sql.*;
/**
* $Id: database-access.html,v 1.1 2009/06/22 16:12:08 kishi Exp kishi $
* 更新系のテスト
*/
public class Test2 extends AbstractDataBaseAccessor {
public Test2() {
super();
}
static public void main( String[] args ) {
Test2 test2 = new Test2();
test2.dumpProperties();
try {
test2.setSQL( "update node set linkNodeId = null where nodeID > ?" );
test2.setParam( 1, new Integer( 2 ) );
int resultCode = test2.executeUpdate();
System.out.println( "処理結果コード=" + resultCode );
test2.cleanup();
} catch ( Exception e ) {
e.printStackTrace();
}
}
}
::::::::::::::
Test3.java
::::::::::::::
import java.util.*;
import java.sql.*;
/**
$Id: database-access.html,v 1.1 2009/06/22 16:12:08 kishi Exp kishi $
DB照会用のテストコード
*/
public class Test3 extends AbstractDataBaseAccessor {
public Test3() {
super();
}
static public void main( String[] args ) {
Test3 test3 = new Test3();
test3.dumpProperties();
try {
test3.setSQL( "SELECT * from material" );
List list = test3.executeQuery();
Iterator iterator = list.iterator();
while ( iterator.hasNext() ) {
Object[] record = ( Object[] ) iterator.next();
for ( int i = 0;i < record.length;i++ ) {
if ( record[ i ] != null ) {
System.out.print( record[ i ].toString() );
} else {
System.out.print( "NULL" );
}
System.out.print( "\t" );
}
System.out.println();
}
test3.cleanup();
} catch ( Exception e ) {
e.printStackTrace();
}
}
}
::::::::::::::
Test4.java
::::::::::::::
import java.util.*;
import java.sql.*;
/**
$Id: database-access.html,v 1.1 2009/06/22 16:12:08 kishi Exp kishi $
DB照会用のテストコード
*/
public class Test4 extends AbstractDataBaseAccessor {
public Test4() {
super();
}
static public void main( String[] args ) {
Test4 test4 = new Test4();
test4.dumpProperties();
try {
test4.setSQL( "insert into material (nodeId, sentence) values (1002, ?)" );
test4.setParam( 1, "「新たな脅威への対応」では、ミサイル防衛（ＭＤ）での情報共有、共同研究の加速や、国際テロ・ゲリラ攻撃を防ぐための情報交換などが浮上している。「国際安全保障環境の改善」では、自衛隊がイラクなどで行ってきた国際協力活動の実績を評価し、こうした分野で日米協力を強化する方針を打ち出す。インドネシア・スマトラ島沖地震・津波のような大規模災害の救援活動のための現地調査を日米共同で行うことなども盛り込む。 両国は、６月４、５の両日にシンガポールで開かれる「アジア安全保障会議」（英国際戦略問題研究所主催）の際に大野防衛長官とラムズフェルド米国防長官が会談する方向で調整している。その前後にラムズフェルド長官が日本に立ち寄り、外務・防衛担当閣僚級の「日米安保協議委員会」（２プラス２）開催も検討しており、こうした際に共同文書を発表する考えだ。" );
int resultCode = test4.executeUpdate();
System.out.println( "処理結果コード=" + resultCode );
test4.cleanup();
} catch ( Exception e ) {
e.printStackTrace();
}
}
}
::::::::::::::
Test5.java
::::::::::::::
import java.util.*;
import java.sql.*;
/**
$Id: database-access.html,v 1.1 2009/06/22 16:12:08 kishi Exp kishi $
DB更新用のテストコード
*/
public class Test5 extends AbstractDataBaseAccessor {
public Test5() {
super();
}
static public void main( String[] args ) {
Test5 test5 = new Test5();
test5.dumpProperties();
try {
test5.setSQL( "insert into node (parentNodeId, title) values (3, ?)" );
test5.setParam( 1, "千代大海がカド番脱出、朝青龍は９連勝…大相撲９日目\n大相撲夏場所９日目（１６日・両国国技館）――朝青龍は、海鵬を左四つに組み止め、上手投げで退けて初日から９連勝。千代大海は、岩木山を土俵際ではたき込んで勝ち越しを決め、７度目の大関カド番を脱出した。" );
int resultCode = test5.executeUpdate();
System.out.println( "処理結果コード=" + resultCode );
test5.cleanup();
} catch ( Exception e ) {
e.printStackTrace();
}
}
}
::::::::::::::
Traverser.java
::::::::::::::
import java.util.*;
/**
$Id: database-access.html,v 1.1 2009/06/22 16:12:08 kishi Exp kishi $
*/
public class Traverser extends AbstractDataBaseAccessor {
public Traverser() {
super();
}
private void getResultList() {
try {
this.setSQL( "select * from node order by nodeId" );
List list = this.executeQuery();
Iterator iterator = list.iterator();
while ( iterator.hasNext() ) {
Object[] record = ( Object[] ) iterator.next();
for ( int i = 0;i < record.length;i++ ) {
if ( record[ i ] != null ) {
System.out.print( record[ i ].toString() );
} else {
System.out.print( "NULL" );
}
System.out.print( "\t" );
}
System.out.println();
}
} catch ( Exception e ) {
e.printStackTrace();
}
}
public static void main( String[] args ) {
Traverser traverser = new Traverser();
traverser.getResultList();
}
}
戻る
