Java DB.getConnection方法代码示例

import play.db.DB; //导入方法依赖的package包/类
public static void initDB()
    throws IOException, SQLException {
  String query = "";
  FileInputStream inputStream = new FileInputStream(TEST_DATA_FILE);

  try {
    query = IOUtils.toString(inputStream);
  } finally {
    inputStream.close();
  }

  Connection connection = DB.getConnection();

  try {
    Statement statement = connection.createStatement();
    statement.execute(query);
  } finally {
    connection.close();
  }
}
 

import play.db.DB; //导入方法依赖的package包/类
public static int loadBitcoindClusters(){
    Connection c = DB.getConnection();
    try {
        PreparedStatement ps = c.prepareStatement("SELECT id, conn_string, rpc_username, rpc_password FROM bitcoind_clusters ORDER BY id ASC");
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            String id = rs.getString("id");
            String connString = rs.getString("conn_string");
            String rpcUsername = rs.getString("rpc_username");
            String rpcPassword = rs.getString("rpc_password");
            BitcoindClientFactory bcf = new BitcoindClientFactory(new URL(connString), rpcUsername, rpcPassword);
            clusters.add( new ClusterInfo(id, connString, bcf) );
        }
        c.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return clusters.size();
}
 

import play.db.DB; //导入方法依赖的package包/类
private static boolean writeClusterAssignmentToDB(String user, Integer clusterAssignment){
    Connection c = DB.getConnection();
    if(c == null)
        return false;
    try {
        PreparedStatement ps =
             c.prepareStatement("INSERT INTO account_holders" +
              "(account_name, cluster_id, confirmed_satoshi_balance, unconfirmed_satoshi_balance) VALUES(?, ?, ?, ?)");
        ps.setString(1, user);
        ps.setInt(2, clusterAssignment);
        ps.setLong(3, 0);
        ps.setLong(4, 0);
        int affectedRows = ps.executeUpdate();
        c.close();
        boolean res = (affectedRows == 1);
        return res;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
private static Integer checkClusterAssignmentFromDB(String user){
    Connection c = DB.getConnection();
    if(c == null)
        return null;
    try {
        PreparedStatement ps = c.prepareStatement("SELECT cluster_id FROM account_holders WHERE account_name = ?");
        ps.setString(1, user);
        ResultSet rs = ps.executeQuery();
        if(rs == null) return null;
        if(!rs.next()) return null;
        Integer result = rs.getInt(1);
        c.close();

        for(int i = 0; i < clusters.size(); i++){
            if( clusters.get(i).id.equals(String.valueOf(result)) )
                return i;
        }
        return null;
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
private boolean initializeSchema(){
    Connection c = DB.getConnection();
    try {
        Statement s = c.createStatement();
        s.addBatch("CREATE TABLE \"bitcoind_clusters\" (\"id\" serial PRIMARY KEY, \"conn_string\" TEXT, \"rpc_username\" TEXT, \"rpc_password\" TEXT) WITH (OIDS=FALSE);");
        s.addBatch("CREATE TABLE \"account_holders\" (\"account_id\" bigserial PRIMARY KEY, \"account_name\" TEXT, \"cluster_id\" int, \"confirmed_satoshi_balance\" bigint, \"unconfirmed_satoshi_balance\" bigint) WITH (OIDS=FALSE);");
        s.addBatch("ALTER TABLE \"account_holders\" ADD CONSTRAINT account_holders_fk0 FOREIGN KEY (cluster_id) REFERENCES bitcoind_clusters(id);");
        s.addBatch("CREATE TABLE \"transactions\" (\"internal_txid\" bigserial PRIMARY KEY, \"matched_user_id\" bigint, \"inbound\" BOOLEAN, \"tx_hash\" TEXT, \"confirmed\" BOOLEAN, \"amount_satoshi\" bigint) WITH (OIDS=FALSE);");
        s.addBatch("ALTER TABLE \"transactions\" ADD CONSTRAINT transactions_fk0 FOREIGN KEY (matched_user_id) REFERENCES account_holders(account_id);");
        s.addBatch("CREATE TABLE \"used_txos\" (\"used_txo_id\" bigserial PRIMARY KEY, \"txo\" TEXT, \"new_txid\" TEXT ) WITH (OIDS=FALSE);");
        s.addBatch("ALTER TABLE \"used_txos\" ADD CONSTRAINT used_txos_fk0 FOREIGN KEY (associated_internaltx_id) REFERENCES transactions(internal_txid);");
        s.addBatch("CREATE INDEX used_txos_txo_idx ON used_txos(txo);");
        s.addBatch("CREATE INDEX transactions_matched_user_id_idx ON transactions(matched_user_id);");
        s.addBatch("CREATE INDEX account_holders_account_name_idx ON account_holders(account_name);");
        s.addBatch("CREATE INDEX used_txos_new_txid_idx ON used_txos(new_txid);");

        s.executeBatch();
        return true;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
private boolean tableExists(String tableName){
    try {
        Connection c = DB.getConnection();
        PreparedStatement ps = c.prepareStatement("SELECT relname FROM pg_class WHERE relname = ?");
        ps.setString(1, tableName);

        boolean exists = false;
        ResultSet rs = ps.executeQuery();
        if(rs.next())
            exists = rs.getString("relname").equals(tableName);

        c.close();
        return exists;
    }
    catch (SQLException e){
        e.printStackTrace();
        return false;
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
public static Result getUser(String name) {
    Connection c = DB.getConnection();
    try {
        PreparedStatement ps = c.prepareStatement("SELECT * FROM account_holders WHERE account_name = ?");
        ps.setString(1, name);
        ResultSet rs = ps.executeQuery();
        if(rs == null || !rs.next()) {
            c.close();
            return internalServerError("User not found");
        }
        c.close();
        ObjectNode root = mapper.createObjectNode();
        root.put("user_id", rs.getLong("account_id"));
        root.put("account_name", rs.getString("account_name"));
        root.put("cluster_id", rs.getLong("cluster_id"));
        root.put("confirmed_satoshi_balance", rs.getLong("confirmed_satoshi_balance"));
        root.put("unconfirmed_satoshi_balance", rs.getLong("unconfirmed_satoshi_balance"));
        return ok(root);
    } catch(Exception e){
        e.printStackTrace();
        return internalServerError("An error occurred while fetching user information");
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
private static TxDatabasePresence txPresentInDB(String txHash){
    Connection c = DB.getConnection();
    try {
        PreparedStatement ps =
                c.prepareStatement("SELECT internal_txid, matched_user_id, inbound, tx_hash, confirmed FROM transactions WHERE tx_hash = ?");
        ps.setString(1, txHash);
        ResultSet rs = ps.executeQuery();
        c.close();
        if(rs == null)
            return TxDatabasePresence.ERROR;
        if(!rs.next())
            return TxDatabasePresence.NOT_PRESENT;
        if(!rs.getString("tx_hash").equals(txHash))
            return TxDatabasePresence.NOT_PRESENT;

        if(rs.getBoolean("confirmed"))
            return TxDatabasePresence.CONFIRMED;
        else
            return TxDatabasePresence.UNCONFIRMED;
    }
    catch(Exception e){
        e.printStackTrace();
        return TxDatabasePresence.ERROR;
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
private static long getIdFromAccountName(String accountName){
    Connection c = DB.getConnection();
    try {
        PreparedStatement ps =
                c.prepareStatement("SELECT account_id FROM account_holders WHERE account_name = ?");
        ps.setString(1, accountName);
        ResultSet rs = ps.executeQuery();
        c.close();

        if(rs == null)
            return -1;
        if(!rs.next())
            return -1;
        return rs.getLong("account_id");
    }
    catch(Exception e){
        e.printStackTrace();
        return -1;
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
private static boolean insertTxIntoDB(String txHash, long userId, boolean inbound, boolean confirmed, long amountSatoshis){
    Connection c = DB.getConnection();
    try {
        PreparedStatement ps =
                c.prepareStatement("INSERT INTO transactions(matched_user_id, inbound, tx_hash, confirmed, amount_satoshi) VALUES(?, ?, ?, ?, ?)");
        ps.setLong(1, userId);
        ps.setBoolean(2, inbound);
        ps.setString(3, txHash);
        ps.setBoolean(4, confirmed);
        ps.setLong(5, amountSatoshis);
        int result = ps.executeUpdate();
        c.close();
        boolean ret = (result == 1);
        return ret;
    }
    catch(Exception e){
        e.printStackTrace();
        return false;
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
private static boolean updateTxStatus(String txHash, boolean confirmed){
    Connection c = DB.getConnection();
    try {
        PreparedStatement ps =
                c.prepareStatement("UPDATE transactions SET confirmed = ? WHERE tx_hash = ?");
        ps.setBoolean(1, confirmed);
        ps.setString(2, txHash);
        int result = ps.executeUpdate();
        c.close();
        boolean ret = (result == 1);
        return ret;
    }
    catch(Exception e){
        e.printStackTrace();
        return false;
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
private static long getUserBalance(long userId, boolean confirmed){
    Connection c = DB.getConnection();
    try {
        String fetchCol = confirmed ? "confirmed_satoshi_balance" : "unconfirmed_satoshi_balance";
        PreparedStatement ps =
                c.prepareStatement("SELECT " + fetchCol + " FROM account_holders WHERE account_id = ?");

        ps.setLong(1, userId);
        ResultSet rs = ps.executeQuery();
        c.close();

        if(rs == null)
            return -1;
        if(!rs.next())
            return -1;
        return rs.getLong(fetchCol);
    }
    catch(Exception e){
        e.printStackTrace();
        return -1;
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
private static boolean updateUserBalance(long userId, boolean confirmed, long updatedValue){
    Connection c = DB.getConnection();
    try {
        String updateCol = confirmed ? "confirmed_satoshi_balance" : "unconfirmed_satoshi_balance";
        PreparedStatement ps =
                c.prepareStatement("UPDATE account_holders SET " + updateCol + " = ? WHERE account_id = ?");

        ps.setLong(1, updatedValue);
        ps.setLong(2, userId);

        int updatedRows = ps.executeUpdate();
        c.close();

        boolean res = (updatedRows == 1);
        return res;
    }
    catch(Exception e){
        e.printStackTrace();
        return false;
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
private static boolean addTxInputsToDB(RawTransaction tx) {
    if(tx == null)
        return false;
    List<String> inputTxIds = tx.extractInputTxIds();
    if(inputTxIds.size() == 0)
        return false;

    Connection conn = DB.getConnection();
    try {
        for(String txid : inputTxIds){
            PreparedStatement ps = conn.prepareStatement("INSERT INTO used_txos (txo, new_txid) VALUES (?, ?)");
            ps.setString(1, txid);
            ps.setString(2, tx.getTxid());
            int res = ps.executeUpdate();
            if(res == 0)
                return false;
        }
        conn.close();
        return true;
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
}
 

import play.db.DB; //导入方法依赖的package包/类
public static ArrayList<String> get_user_ids()
{
	try(Connection conn = DB.getConnection()) {
		PreparedStatement stmt = conn.prepareStatement("SELECT * FROM `User` WHERE gcm_id IS NOT NULL");
		ResultSet s =stmt.executeQuery();
		ArrayList<String> user_ids = new ArrayList<String>();
		while(s.next())
		{
			String gcm_id = s.getString("gcm_id");
			user_ids.add(gcm_id);
			System.out.println(gcm_id+"\n");
		}
		s.close();
		return user_ids;
	}catch(Exception e)
	{
		return null;
	}

}