Example usage for org.springframework.jdbc.core JdbcTemplate execute

Introduction

In this page you can find the example usage for org.springframework.jdbc.core JdbcTemplate execute.

Prototype

@Override
    public void execute(final String sql) throws DataAccessException 

Source Link

Usage

From source file:job_agency.job_agency.beans.DatabaseBean.java

public void create() throws Exception {
    JdbcTemplate jdbc = new JdbcTemplate(dataSource);

    String sqlperson = "create table Person (\n"
            + "  id integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),\n"
            + "  username varchar(20),\n" + "  firstname varchar(20),\n" + "  lastname varchar(20),\n"
            + "  sex varchar(10),\n" + "  birthday Date,\n" + "  postalcode varchar(20),\n"
            + "  city varchar(20),\n" + "  country varchar(3),\n" + "  educationself varchar(30),\n"
            + "  educationmother varchar(30),\n" + "  educationfather varchar(30),\n" + "  email varchar(30),\n"
            + "  location varchar(30),\n" + "  interest varchar(30),\n" + "  newsletter boolean\n" + ")";

    String sqljoboffer = "create table joboffer (\n"
            + "  id integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),\n"
            + "  title varchar(20),\n" + "  postalcode varchar(20),\n" + "  city varchar(20),\n"
            + "  country varchar(3),\n" + "  phone varchar(30),\n" + "  email varchar(30),\n"
            + "  jobdescription varchar(300),\n" + "  salary varchar(30),\n" + "  keyword varchar(200),\n"
            + "  newsletter boolean\n" + ")";

    LOG.info("Creating table Person ...");
    LOG.info("Creating table Joboffer ...");

    try {/*  w  w w  .  j  a  v  a  2 s  .com*/
        jdbc.execute("drop table person");
        jdbc.execute("drop table joboffer");
    } catch (Throwable e) {
        // ignore
    }

    jdbc.execute(sqlperson);
    jdbc.execute(sqljoboffer);

    LOG.info("... created table person");
    LOG.info("... created table joboffer");
}

---

From source file:au.aurin.org.svc.GeodataFinder.java

public Integer InsertAgreement(final long user_id) {

    LOGGER.info("InsertAgreement for user_id {} ", user_id);

    try {/*from   ww  w.  ja  v  a  2 s . c o  m*/
        final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        final String query = "insert into  agreement(agrname,lic_id,user_id, app_id) "
                + " select 'aggrname', c.lic_id, a.user_id, b.app_id from user_orgs as a,  user_apps as b, license as c "
                + " where a.user_id = b.user_id and c.org_id = a.org_id and a.user_id = " + user_id;

        LOGGER.info(" query InsertAgreement  is {} ", query);
        jdbcTemplate.execute(query);
        return 1;

    } catch (final Exception e) {
        LOGGER.info("InsertAgreement failed  error is: ", e.toString());
        return 0;

    }

}

---

From source file:au.aurin.org.svc.GeodataFinder.java

public Boolean addRole(final String role) {

    LOGGER.info("Inside addRole, role {} ", role);

    try {//from   www  . j ava 2s  . c  o m
        final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        String query = "select count(*) from roles where lower(rolename) ='" + role.toLowerCase() + "'";

        LOGGER.info(" query addRole  is {} ", query);
        final Integer out = jdbcTemplate.queryForInt(query);
        if (out == 0) {
            query = "Insert into roles (rolename) values('" + role + "')";

            LOGGER.info(" query addRole  is {} ", query);
            jdbcTemplate.execute(query);
            return true;
        } else {
            return false;
        }

    } catch (final Exception e) {
        LOGGER.info("addRole failed  error is: ", e.toString());
        return false;
    }

}

---

From source file:au.aurin.org.svc.GeodataFinder.java

public Boolean addAcc(final String acc) {

    LOGGER.info("Inside adAcc, acc {} ", acc);

    try {/* w ww .j av a  2s .c om*/
        final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        String query = "select count(*) from acclvls where lower(acclvlname) ='" + acc.toLowerCase() + "'";

        LOGGER.info(" query addAcc  is {} ", query);
        final Integer out = jdbcTemplate.queryForInt(query);
        if (out == 0) {

            query = "Insert into acclvls (acclvlname) values('" + acc + "')";

            LOGGER.info(" query addAcc  is {} ", query);
            jdbcTemplate.execute(query);
            return true;
        } else {
            return false;
        }

    } catch (final Exception e) {
        LOGGER.info("addAcc failed  error is: ", e.toString());
        return false;
    }

}

---

From source file:au.aurin.org.svc.GeodataFinder.java

public Boolean addApp(final String app) {

    LOGGER.info("Inside adAapp, app {} ", app);

    try {// w  w  w. j a  v  a  2s  . c  o  m
        final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        String query = "select count(*) from application where lower(appname) ='" + app.toLowerCase() + "'";

        LOGGER.info(" query adAapp  is {} ", query);
        final Integer out = jdbcTemplate.queryForInt(query);
        if (out == 0) {

            query = "Insert into application (appname,appcontact,appurl) values('" + app + "', '','')";

            LOGGER.info(" query addApp  is {} ", query);
            jdbcTemplate.execute(query);
            return true;
        } else {
            return false;
        }

    } catch (final Exception e) {
        LOGGER.info("addApp failed  error is: ", e.toString());
        return false;
    }

}

---

From source file:au.aurin.org.svc.GeodataFinder.java

public Boolean addOrg(final String org) {

    LOGGER.info("Inside addOrg, Org {} ", org);

    try {/*from www. ja  v a2s .  c  o m*/
        final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        String query = "select count(*) from organisations where lower(orgname) ='" + org.toLowerCase() + "'";

        LOGGER.info(" query addOrg  is {} ", query);
        final Integer out = jdbcTemplate.queryForInt(query);
        if (out == 0) {

            query = "Insert into organisations (orgname,orgemail,orgurl) values('" + org + "', '','')";

            LOGGER.info(" query addOrg  is {} ", query);
            jdbcTemplate.execute(query);
            return true;
        } else {
            return false;
        }

    } catch (final Exception e) {
        LOGGER.info("addOrg failed  error is: ", e.toString());
        return false;
    }

}

---

From source file:au.aurin.org.svc.GeodataFinder.java

public long InsertUser(String email, String firstname, String lastname, final String password,
        final String randomUUIDString) {

    LOGGER.info("InsertUser, email {} ", email);

    try {/* w  w  w  .  ja v a2s .co  m*/
        final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        email = email.replace("'", "''");
        firstname = firstname.replace("'", "''");
        lastname = lastname.replace("'", "''");
        String query = "select count(*) as cnt from users where lower(email) = '" + email.toLowerCase() + "'";

        final int cnt = jdbcTemplate.queryForInt(query);
        if (cnt == 0) {
            query = "Insert into users (email, firstname,lastname,password, uuid) values('" + email + "','"
                    + firstname + "','" + lastname + "','" + password + "','" + randomUUIDString + "')";

            LOGGER.info(" query insert table  is {} ", query);
            jdbcTemplate.execute(query);

            final long user_id = jdbcTemplate.queryForLong("SELECT max(user_id) FROM users");

            return user_id;
        } else {
            return 0;
        }
    } catch (final Exception e) {
        LOGGER.info("InsertUser failed  error is: {} ", e.toString());
        return 0;
    }

}

---

From source file:org.opendatakit.persistence.engine.pgres.DatastoreImpl.java

private void createIndex(JdbcTemplate jc, CommonFieldsBase tbl, String idxName, DataField field) {
    StringBuilder b = new StringBuilder();

    b.append(K_CREATE_INDEX);/*from w  w  w.  j  av a  2 s .  c o  m*/
    b.append(K_BQ);
    b.append(idxName);
    b.append(K_BQ);
    b.append(K_ON);
    b.append(K_BQ);
    b.append(tbl.getSchemaName());
    b.append(K_BQ);
    b.append(".");
    b.append(K_BQ);
    b.append(tbl.getTableName());
    b.append(K_BQ);
    if (field.getIndexable() == IndexType.HASH) {
        b.append(K_USING_HASH);
    }
    b.append(" (");
    b.append(K_BQ);
    b.append(field.getName());
    b.append(K_BQ);
    b.append(" )");

    jc.execute(b.toString());
}

---

From source file:org.opendatakit.persistence.engine.pgres.DatastoreImpl.java

/**
 * Relation manipulation APIs/*from   w  w  w  .  j ava  2s .c o m*/
 */
@Override
public void assertRelation(CommonFieldsBase relation, User user) throws ODKDatastoreException {
    JdbcTemplate jc = getJdbcConnection();
    TransactionStatus status = null;
    try {
        DefaultTransactionDefinition paramTransactionDefinition = new DefaultTransactionDefinition();

        // do serializable read on the information schema...
        paramTransactionDefinition.setIsolationLevel(DefaultTransactionDefinition.ISOLATION_SERIALIZABLE);
        paramTransactionDefinition.setReadOnly(true);
        status = tm.getTransaction(paramTransactionDefinition);

        // see if relation already is defined and update it with dimensions...
        if (updateRelation(jc, relation, null)) {
            // it exists -- we're done!
            tm.commit(status);
            status = null;
            return;
        } else {
            tm.commit(status);
            // Try a new transaction to create the table
            paramTransactionDefinition.setIsolationLevel(DefaultTransactionDefinition.ISOLATION_SERIALIZABLE);
            paramTransactionDefinition.setReadOnly(false);
            status = tm.getTransaction(paramTransactionDefinition);

            // total number of columns must be less than MAX_BIND_PARAMS
            int countColumns = 0;
            // need to create the table...
            StringBuilder b = new StringBuilder();
            b.append(K_CREATE_TABLE);
            b.append(K_BQ);
            b.append(relation.getSchemaName());
            b.append(K_BQ);
            b.append(".");
            b.append(K_BQ);
            b.append(relation.getTableName());
            b.append(K_BQ);
            b.append(K_OPEN_PAREN);
            boolean firstTime = true;
            for (DataField f : relation.getFieldList()) {
                if (!firstTime) {
                    b.append(K_CS);
                }
                ++countColumns;
                firstTime = false;
                b.append(K_BQ);
                b.append(f.getName());
                b.append(K_BQ);
                DataField.DataType type = f.getDataType();
                switch (type) {
                case BINARY:
                    b.append(" BYTEA");
                    break;
                case LONG_STRING:
                    b.append(" TEXT");// b.append(" CHARACTER SET utf8");
                    break;
                case STRING:
                    b.append(" VARCHAR(");
                    Long len = f.getMaxCharLen();
                    if (len == null) {
                        len = PersistConsts.DEFAULT_MAX_STRING_LENGTH;
                    }
                    b.append(len.toString());
                    b.append(K_CLOSE_PAREN);
                    // b.append(" CHARACTER SET utf8");
                    break;
                case BOOLEAN:
                    b.append(" BOOLEAN");
                    break;
                case INTEGER:
                    Integer int_digits = f.getNumericPrecision();
                    if (int_digits == null) {
                        int_digits = DEFAULT_INT_NUMERIC_PRECISION;
                    }

                    if (int_digits.compareTo(9) > 0) {
                        b.append(" BIGINT");
                    } else {
                        b.append(" INTEGER");
                    }
                    break;
                case DECIMAL:
                    if (f.isDoublePrecision()) {
                        b.append(" FLOAT(53)");
                    } else {
                        Integer dbl_digits = f.getNumericPrecision();
                        Integer dbl_fract = f.getNumericScale();
                        if (dbl_digits == null) {
                            dbl_digits = DEFAULT_DBL_NUMERIC_PRECISION;
                        }
                        if (dbl_fract == null) {
                            dbl_fract = DEFAULT_DBL_NUMERIC_SCALE;
                        }
                        b.append(" DECIMAL(");
                        b.append(dbl_digits.toString());
                        b.append(K_CS);
                        b.append(dbl_fract.toString());
                        b.append(K_CLOSE_PAREN);
                    }
                    break;
                case DATETIME:
                    b.append(" TIMESTAMP WITHOUT TIME ZONE");
                    break;
                case URI:
                    b.append(" VARCHAR(");
                    len = f.getMaxCharLen();
                    if (len == null) {
                        len = PersistConsts.URI_STRING_LEN;
                    }
                    b.append(len.toString());
                    b.append(")");// b.append(" CHARACTER SET utf8");
                    break;
                }

                if (f == relation.primaryKey) {
                    b.append(" UNIQUE ");
                }
                if (f.getNullable()) {
                    b.append(" NULL ");
                } else {
                    b.append(" NOT NULL ");
                }
            }
            b.append(K_CLOSE_PAREN);

            if (countColumns > MAX_BIND_PARAMS) {
                throw new IllegalArgumentException("Table size exceeds bind parameter limit");
            }

            String createTableStmt = b.toString();
            LogFactory.getLog(DatastoreImpl.class).info("Attempting: " + createTableStmt);

            jc.execute(createTableStmt);
            LogFactory.getLog(DatastoreImpl.class)
                    .info("create table success (before updateRelation): " + relation.getTableName());

            String idx;
            // create other indicies
            for (DataField f : relation.getFieldList()) {
                if ((f.getIndexable() != IndexType.NONE) && (f != relation.primaryKey)) {
                    idx = relation.getTableName() + "_" + shortPrefix(f.getName());
                    createIndex(jc, relation, idx, f);
                }
            }

            // and update the relation with actual dimensions...
            updateRelation(jc, relation, createTableStmt);
            tm.commit(status);
        }
    } catch (Exception e) {
        if (status != null) {
            tm.rollback(status);
        }
        throw new ODKDatastoreException(e);
    }
}

---

From source file:no.kantega.publishing.common.util.database.dbConnectionFactory.java

private static void createTables(DataSource dataSource) {
    String productName = null;/*  w  w  w  . ja  v a2s. c om*/

    try (Connection c = dataSource.getConnection()) {
        productName = c.getMetaData().getDatabaseProductName();

    } catch (SQLException e) {
        throw new SystemException("Error creating tables for Flyt CMS", e);
    }

    String dbType = getDBVendor(productName);

    final URL resource = dbConnectionFactory.class.getClassLoader()
            .getResource("dbschema/aksess-database-" + dbType + ".sql");

    if (resource != null) {
        log.info("Creating tables from schema definition " + resource);
        final InputStream schema;
        try {
            schema = resource.openStream();
        } catch (IOException e) {
            throw new SystemException("Can't load schema resource " + resource, e);
        }
        try {

            final String[] statements = IOUtils.toString(schema).split(";");

            JdbcTemplate template = new JdbcTemplate(dataSource);
            for (String statement : statements) {
                String[] lines = statement.split("\n");
                StringBuilder stripped = new StringBuilder();
                for (String line : lines) {
                    if (line.trim().length() != 0 && !line.trim().startsWith("#")
                            && !line.trim().startsWith("--")) {
                        stripped.append(line).append('\n');
                    }
                }
                String query = stripped.toString();
                if (query.length() > 0) {
                    template.execute(query);
                }
            }

        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• »