Java SQLiteConnection.prepare方法代码示例

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
private CardsManager() throws SQLiteException {
	SQLiteConnection db = new SQLiteConnection(new File(YGOCoreMain.getConfigurator().getDataBasePath()));
	db.open(true);
	SQLiteStatement st = db.prepare("SELECT id, ot, alias, type, level, race, attribute, atk, def FROM datas");
	try {
		while(st.step()) {
			int id = st.columnInt(0);
			Card c = new Card(id, st.columnInt(1));
			c.alias = st.columnInt(2);
			c.setcode = st.columnInt(3);
			int levelinfo = st.columnInt(4);
			c.level = levelinfo & 0xff;
			c.lscale = (levelinfo >> 24) & 0xff;
			c.rscale = (levelinfo >> 16) & 0xff;
			c.race = st.columnInt(6);
			c.attr = st.columnInt(7);
			c.attack = st.columnInt(8);
			c.defense = st.columnInt(9);
			mCards.put(id, c);
		}
	} finally {
		st.dispose();
	}
	db.dispose();
	
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
public SQLiteQuery(SQLiteConnection connection, String sqlQuery, boolean cacheStmt)
		throws SQLiteException {
	super();

	this.queryString = sqlQuery;
	this.stmt = connection.prepare(sqlQuery, cacheStmt);

	HashMap<String, Integer> colIdxByColName = new HashMap<String, Integer>();

	int nbCols = stmt.columnCount();
	for (int colIdx = 0; colIdx < nbCols; colIdx++) {
		String colName = stmt.getColumnName(colIdx);
		colIdxByColName.put(colName, colIdx);
	}

	this.resultDesc = new SQLiteResultDescriptor(colIdxByColName);
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
public AbstractSpectrumSliceIterator(
	AbstractSpectrumHeaderReader spectrumHeaderReader,
	AbstractDataEncodingReader dataEncodingReader,
	SQLiteConnection connection,
	String sqlQuery
) throws SQLiteException, StreamCorruptedException {
	
	// Create a new statement (will be automatically closed by the StatementIterator)
	SQLiteStatement stmt = connection.prepare(sqlQuery, true); // true = cached enabled

	// Set some fields
	this.boundingBoxIterator = new BoundingBoxIterator(
		spectrumHeaderReader,
		dataEncodingReader,
		connection,
		stmt
	);
	this.statement = stmt;

	initBB();
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
@Override
public List<IaPrefix> findExpiredIaPrefixes() {
	SQLiteConnection connection = null;
	SQLiteStatement statement = null;
	try {
		connection = getSQLiteConnection();
		statement = connection.prepare(
					"select * from dhcplease" +
	                " where iatype = " + IdentityAssoc.PD_TYPE +
	                " and validendtime < ? order by validendtime");
           statement.bind(1, new Date().getTime());
           List<DhcpLease> leases = mapLeases(statement);
           return toIaPrefixes(leases);
	}
	catch (SQLiteException ex) {
		log.error("findExpiredIaPrefixes failed", ex);
		throw new RuntimeException(ex);
	}
	finally {
		closeStatement(statement);
		closeConnection(connection);
	}
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
protected List<DhcpLease> findExpiredLeases(final byte iatype) {
	SQLiteConnection connection = null;
	SQLiteStatement statement = null;
	try {
		connection = getSQLiteConnection();
		statement = connection.prepare(
	                "select * from dhcplease" +
	                " where iatype = ?" +
	                " and state != " + IaAddress.STATIC +
	                " and validendtime < ? order by validendtime");
		statement.bind(1, iatype);
		statement.bind(2, new Date().getTime());
		
		return mapLeases(statement);
	}
	catch (SQLiteException ex) {
		log.error("findExpiredLeases failed", ex);
		throw new RuntimeException(ex);
	}
	finally {
		closeStatement(statement);
		closeConnection(connection);
	}
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
/**
 * Find dhcp leases for ia.
 *
 * @param duid the duid
 * @param iatype the iatype
 * @param iaid the iaid
 * @return the list
 */
protected List<DhcpLease> findDhcpLeasesForIA(final byte[] duid, final byte iatype, final long iaid)
{
	SQLiteConnection connection = null;
	SQLiteStatement statement = null;
	try {
		connection = getSQLiteConnection();
		statement = connection.prepare("select * from dhcplease" +
	                " where duid = ?" +
	                " and iatype = ?" +
	                " and iaid = ?");
		statement.bind(1, duid);
           statement.bind(2, iatype);
           statement.bind(3, iaid);			
           return mapLeases(statement);
	}
	catch (SQLiteException ex) {
		log.error("findDhcpLeasesForIA failed", ex);
		throw new RuntimeException(ex);
	}
	finally {
		closeStatement(statement);
		closeConnection(connection);
	}
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
@Override
public List<IaPrefix> findUnusedIaPrefixes(final InetAddress startAddr, final InetAddress endAddr) {
	long offerExpireMillis = 
		DhcpServerPolicies.globalPolicyAsLong(Property.BINDING_MANAGER_OFFER_EXPIRATION);
	final long offerExpiration = new Date().getTime() - offerExpireMillis;
	
	SQLiteConnection connection = null;
	SQLiteStatement statement = null;
	try {
		connection = getSQLiteConnection();
		statement = connection.prepare(
	                "select * from dhcplease" +
	                " where ((state=" + IaPrefix.ADVERTISED +
	                " and starttime <= ?)" +
	                " or (state=" + IaPrefix.EXPIRED +
	                " or state=" + IaPrefix.RELEASED + "))" +
	                " and ipaddress >= ? and ipaddress <= ?" +
	                " order by state, validendtime, ipaddress");
		statement.bind(1, offerExpiration);
		statement.bind(2, startAddr.getAddress());
		statement.bind(3, endAddr.getAddress());
		
		List<DhcpLease> leases = mapLeases(statement);
		return toIaPrefixes(leases);
	}
	catch (SQLiteException ex) {
		log.error("findUnusedIaPrefixes failed", ex);
		throw new RuntimeException(ex);
	}
	finally {
		closeStatement(statement);
		closeConnection(connection);
	}
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
/**
 * Returns all columns in the table and their type
 * 
 * @param db
 * @param tablename
 * @return
 * @throws SQLiteException
 */
private static Map<String, String> getColumnsInTable(SQLiteConnection db, String tablename) throws SQLiteException
{
	Map<String, String> columnsInTable = Maps.newHashMap();
	SQLiteStatement sColumns = db.prepare("pragma table_info(" + tablename + ");");
	while (sColumns.step())
	{
		columnsInTable.put(sColumns.columnString(1).toLowerCase(), sColumns.columnString(2).toUpperCase());
	}
	sColumns.dispose();

	return columnsInTable;
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
protected long getRecordCount(Configuration conf) throws IOException
{
  String countQuery = "SELECT COUNT(*) FROM tiles WHERE zoom_level=?";
  // Run the count query and grab the result.
  SQLiteConnection conn = null;
  try {
    conn = MbVectorTilesDataProvider.getDbConnection(dbSettings, conf);
    SQLiteStatement stmt = null;
    try {
      stmt = conn.prepare(countQuery, false);
      stmt.bind(1, zoomLevel);
      if (stmt.step()) {
        return stmt.columnLong(0);
      }
      else {
        throw new IOException("Unable to count tiles for zoom " + zoomLevel + " in " + dbSettings.getFilename());
      }
    }
    finally {
      if (stmt != null) {
        stmt.dispose();
      }
    }
  }
  catch (SQLiteException e)
  {
    String msg = "Unable to get the count of records using query: " + countQuery;
    log.error(msg, e);
    throw new IOException(msg, e);
  }
  finally {
    if (conn != null) {
      conn.dispose();
    }
  }
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
/**
 * Insert dhcp lease.
 *
 * @param lease the lease
 */
protected void insertDhcpLease(final DhcpLease lease)
{
	SQLiteConnection connection = null;
	SQLiteStatement statement = null;
	try {
		connection = getSQLiteConnection();
		statement = connection.prepare("insert into dhcplease" +
					" (ipaddress, duid, iatype, iaid, prefixlen, state," +
					" starttime, preferredendtime, validendtime," +
					" ia_options, ipaddr_options)" +
					" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");			
		statement.bind(1, lease.getIpAddress().getAddress());
		statement.bind(2, lease.getDuid());
		statement.bind(3, lease.getIatype());
		statement.bind(4, lease.getIaid());
		statement.bind(5, lease.getPrefixLength());
		statement.bind(6, lease.getState());
		statement.bind(7, lease.getStartTime().getTime());
		statement.bind(8, lease.getPreferredEndTime().getTime());
		statement.bind(9, lease.getValidEndTime().getTime());
		statement.bind(10, encodeOptions(lease.getIaDhcpOptions()));
		statement.bind(11, encodeOptions(lease.getIaAddrDhcpOptions()));
		
		while (statement.step()) {
			log.debug("insertDhcpLease: step=true");
		}
	}
	catch (SQLiteException ex) {
		log.error("insertDhcpLease failed", ex);
		throw new RuntimeException(ex);
	}
	finally {
		closeStatement(statement);
		closeConnection(connection);
	}
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
/**
 * Update dhcp lease.
 *
 * @param lease the lease
 */
protected void updateDhcpLease(final DhcpLease lease)
{
	SQLiteConnection connection = null;
	SQLiteStatement statement = null;
	try {
		connection = getSQLiteConnection();
		statement = connection.prepare("update dhcplease" +
					" set state=?," +
					" starttime=?," +
					" preferredendtime=?," +
					" validendtime=?," +
					" ia_options=?," +
					" ipaddr_options=?" +
					" where ipaddress=?");
		statement.bind(1, lease.getState());
		statement.bind(2, lease.getStartTime().getTime());
		statement.bind(3, lease.getPreferredEndTime().getTime());
		statement.bind(4, lease.getValidEndTime().getTime());
		statement.bind(5, encodeOptions(lease.getIaDhcpOptions()));
		statement.bind(6, encodeOptions(lease.getIaAddrDhcpOptions()));
		statement.bind(7, lease.getIpAddress().getAddress());
		
		while (statement.step()) {
			log.debug("updateDhcpLease: step=true");
		}
	}
	catch (SQLiteException ex) {
		log.error("updateDhcpLease failed", ex);
		throw new RuntimeException(ex);
	}
	finally {
		closeStatement(statement);
		closeConnection(connection);
	}
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
/**
 * Update ipaddr options.
 */
protected void updateIpAddrOptions(final InetAddress inetAddr,
								final Collection<DhcpOption> ipAddrOptions)
{
	SQLiteConnection connection = null;
	SQLiteStatement statement = null;
	try {
		connection = getSQLiteConnection();
		statement = connection.prepare("update dhcplease" +
					" set ipaddr_options=?" +
					" where ipaddress=?");
		statement.bind(1, encodeOptions(ipAddrOptions));
		statement.bind(2, inetAddr.getAddress());
		
		while (statement.step()) {
			log.debug("updateIpAddrOptions: step=true");
		}
	}
	catch (SQLiteException ex) {
		log.error("updateIpAddrOptions failed", ex);
		throw new RuntimeException(ex);
	}
	finally {
		closeStatement(statement);
		closeConnection(connection);
	}
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
/**
 * Find dhcp lease for InetAddr.
 *
 * @param inetAddr the InetAddr
 * @return the DhcpLease
 */
protected DhcpLease findDhcpLeaseForInetAddr(final InetAddress inetAddr)
{
	List<DhcpLease> leases = null;
	SQLiteConnection connection = null;
	SQLiteStatement statement = null;
	try {
		connection = getSQLiteConnection();
		statement = connection.prepare(
	                "select * from dhcplease" +
	                " where ipaddress = ?");
		statement.bind(1, inetAddr.getAddress());
           leases = mapLeases(statement);
           if ((leases != null) && (leases.size() > 0)) {
           	if (leases.size() == 1) {
           		return leases.get(0);
           	}
           	else {
           		//TODO: this really should be impossible because of the unique
           		//		constraint on the IP address
           		log.error("Found more than one lease for IP=" + 
           					inetAddr.getHostAddress());
           	}
           }
	}
	catch (SQLiteException ex) {
		log.error("findDhcpLeaseForInetAddr failed", ex);
		throw new RuntimeException(ex);
	}
	finally {
		closeStatement(statement);
		closeConnection(connection);
	}
       return null;
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
@Override
public List<IaAddress> findUnusedIaAddresses(final InetAddress startAddr, final InetAddress endAddr)
{
	long offerExpireMillis = 
		DhcpServerPolicies.globalPolicyAsLong(Property.BINDING_MANAGER_OFFER_EXPIRATION);
	final long offerExpiration = new Date().getTime() - offerExpireMillis;
	
	SQLiteConnection connection = null;
	SQLiteStatement statement = null;
	try {
		connection = getSQLiteConnection();
		statement = connection.prepare(
	                "select * from dhcplease" +
	                " where ((state=" + IaAddress.ADVERTISED +
	                " and starttime <= ?)" +
	                " or (state=" + IaAddress.EXPIRED +
	                " or state=" + IaAddress.RELEASED + "))" +
	                " and ipaddress >= ? and ipaddress <= ?" +
	                " order by state, validendtime, ipaddress");
		statement.bind(1, offerExpiration);
		statement.bind(2, startAddr.getAddress());
		statement.bind(3, endAddr.getAddress());
		
		List<DhcpLease> leases = mapLeases(statement);
		return toIaAddresses(leases);
	}
	catch (SQLiteException ex) {
		log.error("findUnusedIaAddresses failed", ex);
		throw new RuntimeException(ex);
	}
	finally {
		closeStatement(statement);
		closeConnection(connection);
	}
}
 

import com.almworks.sqlite4java.SQLiteConnection; //导入方法依赖的package包/类
@Override
public void setup(OperatorContext context)
{
  db = new SQLiteConnection(new File("/tmp/sqlite.db"));
  java.util.logging.Logger.getLogger("com.almworks.sqlite4java").setLevel(java.util.logging.Level.SEVERE);
  SQLiteStatement st;

  try {
    db.open(true);
    // create the temporary tables here
    for (int i = 0; i < inputSchemas.size(); i++) {
      InputSchema inputSchema = inputSchemas.get(i);
      ArrayList<String> indexes = new ArrayList<String>();
      if (inputSchema == null || inputSchema.columnInfoMap.isEmpty()) {
        continue;
      }
      String columnSpec = "";
      String columnNames = "";
      String insertQuestionMarks = "";
      int j = 0;
      for (Map.Entry<String, ColumnInfo> entry : inputSchema.columnInfoMap.entrySet()) {
        if (!columnSpec.isEmpty()) {
          columnSpec += ",";
          columnNames += ",";
          insertQuestionMarks += ",";
        }
        columnSpec += entry.getKey();
        columnSpec += " ";
        columnSpec += entry.getValue().type;
        if (entry.getValue().isColumnIndex) {
          indexes.add(entry.getKey());
        }
        columnNames += entry.getKey();
        insertQuestionMarks += "?";
        entry.getValue().bindIndex = ++j;
      }
      String createTempTableStmt = "CREATE TEMP TABLE " + inputSchema.name + "(" + columnSpec + ")";
      st = db.prepare(createTempTableStmt);
      st.step();
      st.dispose();
      for (String index : indexes) {
        String createIndexStmt = "CREATE INDEX " + inputSchema.name + "_" + index + "_idx ON " + inputSchema.name + " (" + index + ")";
        st = db.prepare(createIndexStmt);
        st.step();
        st.dispose();
      }
      String insertStmt = "INSERT INTO " + inputSchema.name + " (" + columnNames + ") VALUES (" + insertQuestionMarks + ")";

      insertStatements.add(i, db.prepare(insertStmt));
      // We are calling "DELETE FROM" on the tables and because of the "truncate optimization" in sqlite, it should be fast.
      // See http://sqlite.org/lang_delete.html
      deleteStatements.add(i, db.prepare("DELETE FROM " + inputSchema.name));
    }
    beginStatement = db.prepare("BEGIN");
    commitStatement = db.prepare("COMMIT");
    execStatement = db.prepare(statement);
  } catch (SQLiteException ex) {
    throw new RuntimeException(ex);
  }
}