Examples with PreparedStatement java.sql.PreparedStatement used on opensource projects

Example 66 with PreparedStatement

use of java.sql.PreparedStatement in project platformlayer by platformlayer.

the class JdbcServiceAuthorizationRepository method createAuthorization.

@Override
@JdbcTransaction
public ServiceAuthorization createAuthorization(ProjectId project, ServiceAuthorization authorization) throws RepositoryException {
    try {
        ServiceType serviceType = new ServiceType(authorization.serviceType);
        JdbcConnection connection = connectionProvider.get();
        int serviceId = JdbcRepositoryHelpers.getServiceKey(connection, serviceType);
        int projectId = JdbcRepositoryHelpers.getProjectKey(connection, project);
        final String sql = "INSERT INTO service_authorizations (service, project, data) VALUES (?, ?, ?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = null;
        try {
            ps.setInt(1, serviceId);
            ps.setInt(2, projectId);
            ps.setString(3, authorization.data);
            int updateCount = ps.executeUpdate();
            if (updateCount != 1) {
                throw new IllegalStateException("Unexpected number of rows inserted");
            }
        } finally {
            JdbcUtils.safeClose(rs);
            JdbcUtils.safeClose(ps);
        }
        return authorization;
    } catch (SQLException e) {
        throw new RepositoryException("Error running query", e);
    }
}

Example 67 with PreparedStatement

use of java.sql.PreparedStatement in project platformlayer by platformlayer.

the class JdbcServiceAuthorizationRepository method findServiceAuthorization.

@Override
@JdbcTransaction
public ServiceAuthorization findServiceAuthorization(ServiceType serviceType, ProjectId project) throws RepositoryException {
    try {
        JdbcConnection connection = connectionProvider.get();
        int serviceId = JdbcRepositoryHelpers.getServiceKey(connection, serviceType);
        int projectId = JdbcRepositoryHelpers.getProjectKey(connection, project);
        String sql = "SELECT data FROM service_authorizations WHERE service=? and project=?";
        List<ServiceAuthorization> items = Lists.newArrayList();
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = null;
        try {
            ps.setInt(1, serviceId);
            ps.setInt(2, projectId);
            rs = ps.executeQuery();
            while (rs.next()) {
                items.add(mapRow(serviceType, rs));
            }
        } finally {
            JdbcUtils.safeClose(rs);
            JdbcUtils.safeClose(ps);
        }
        if (items.size() == 0) {
            return null;
        }
        if (items.size() != 1) {
            throw new IllegalStateException("Found duplicate results for primary key: " + serviceType + ":" + project);
        }
        return items.get(0);
    } catch (SQLException e) {
        throw new RepositoryException("Error running query", e);
    }
}

Example 68 with PreparedStatement

use of java.sql.PreparedStatement in project otter by alibaba.

the class TestMysqlUnsignedInt method insertNumeric.

public static void insertNumeric() throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    Properties from = new Properties();
    from.put("user", "root");
    from.put("password", "root");
    from.put("characterEncoding", "utf8");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/erosa", from);
    PreparedStatement pst = conn.prepareStatement("insert into unsignednumeric(id,id1,id2,id3) values (?,?,?,?)");
    pst.setLong(1, Integer.MAX_VALUE * 2L);
    pst.setLong(2, Integer.MAX_VALUE);
    pst.setBigDecimal(3, new BigDecimal("18446744073709551614"));
    pst.setBigDecimal(4, new BigDecimal("9223372036854775807"));
    pst.executeUpdate();
    pst.close();
    conn.close();
}

Example 69 with PreparedStatement

use of java.sql.PreparedStatement in project otter by alibaba.

the class TestMysqlUnsignedInt method main.

public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
    insertNumeric();
    Thread.sleep(1000L);
    Class.forName("com.mysql.jdbc.Driver");
    Properties from = new Properties();
    from.put("user", "root");
    from.put("password", "root");
    from.put("characterEncoding", "utf8");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/erosa", from);
    PreparedStatement pst = conn.prepareStatement("select id,id1,id2,id3 from unsignednumeric");
    ResultSet rs = pst.executeQuery();
    while (rs.next()) {
        //            try {
        //                System.out.println(rs.getInt(1));
        //            } catch (Exception e) {
        //                System.out.println(rs.getLong(1));
        //            }
        System.out.println(rs.getLong(1));
        System.out.println(rs.getLong(2));
        System.out.println(rs.getBigDecimal(3));
        //            System.out.println(rs.getString(3));
        System.out.println(rs.getBigDecimal(4));
        System.out.println("-----------------------------");
    }
    rs.close();
    pst.close();
    conn.close();
}

Example 70 with PreparedStatement

use of java.sql.PreparedStatement in project otter by alibaba.

the class DbDialectTest method test_mysql.

@Test(expectedExceptions = RuntimeException.class)
public void test_mysql() {
    DbDataMedia media = getMysqlMedia();
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, media.getSource());
    want.object(dbDialect).clazIs(MysqlDialect.class);
    final SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    final JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    final int[] pkColumnTypes = { Types.INTEGER, Types.VARCHAR };
    final int[] columnTypes = { Types.CHAR, Types.DECIMAL, Types.BLOB, Types.CLOB, Types.DATE, Types.TIMESTAMP, Types.TIMESTAMP };
    transactionTemplate.execute(new TransactionCallback() {

        public Object doInTransaction(TransactionStatus status) {
            int affect = 0;
            String sql = null;
            // 执行insert
            sql = sqlTemplate.getInsertSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行update
            sql = sqlTemplate.getUpdateSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行deleate
            sql = sqlTemplate.getDeleteSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(pkColumnTypes), toValues(pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行merge
            sql = sqlTemplate.getMergeSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns, null, true);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            throw new RuntimeException("rollback");
        }
    });
}