jdbctemplate - w3toppers.com

How to use SELECT IN clause in JDBCTemplates?

There is a workaround using NamedParameterJdbcTemplate instead of SimpleJdbcDaoSupport, where you can do something like this: List integerList = Arrays.asList(new Integer[] {1, 2, 3}); Map<String,Object> params = Collections.singletonMap(“fields”, integerList); Long id = namedParameterJdbcTemplate.queryForLong(“SELECT * FROM table WHERE field IN (:fields)”, params); This, however, has a potentially catastrophic limitation regarding the number of parameters you can … Read more

Using a prepared statement and variable bind Order By in Java with JDBC driver

Placeholders ? can only be used for parameter values but not with column and sort order directions. So the standard way to do this as is pointed e.g. here is to use String#format() or something similar to append your column name and order value to your query. Another option is to use Spring Data JPA … Read more

how to copy data from file to PostgreSQL using JDBC?

This works… import java.io.FileReader; import java.sql.Connection; import java.sql.DriverManager; import org.postgresql.copy.CopyManager; import org.postgresql.core.BaseConnection; public class PgSqlJdbcCopyStreamsExample { public static void main(String[] args) throws Exception { if(args.length!=4) { System.out.println(“Please specify database URL, user, password and file on the command line.”); System.out.println(“Like this: jdbc:postgresql://localhost:5432/test test password file”); } else { System.err.println(“Loading driver”); Class.forName(“org.postgresql.Driver”); System.err.println(“Connecting to ” + args[0]); … Read more

NamedParameterJdbcTemplate vs JdbcTemplate

When you use JdbcTemplate you give it SQL that has a ? placeholder for each parameter you want substituted into the SQL. When you assign parameters in the code you have to pass in arguments in an array and they get used in the order in which they appear in the array, like this: Object[] … Read more

Jdbctemplate query for string: EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

In JdbcTemplate , queryForInt, queryForLong, queryForObject all such methods expects that executed query will return one and only one row. If you get no rows or more than one row that will result in IncorrectResultSizeDataAccessException . Now the correct way is not to catch this exception or EmptyResultDataAccessException, but make sure the query you are … Read more

How to set up datasource with Spring for HikariCP?

you need to write this structure on your bean configuration (this is your datasource): <bean id=”hikariConfig” class=”com.zaxxer.hikari.HikariConfig”> <property name=”poolName” value=”springHikariCP” /> <property name=”connectionTestQuery” value=”SELECT 1″ /> <property name=”dataSourceClassName” value=”${hibernate.dataSourceClassName}” /> <property name=”maximumPoolSize” value=”${hibernate.hikari.maximumPoolSize}” /> <property name=”idleTimeout” value=”${hibernate.hikari.idleTimeout}” /> <property name=”dataSourceProperties”> <props> <prop key=”url”>${dataSource.url}</prop> <prop key=”user”>${dataSource.username}</prop> <prop key=”password”>${dataSource.password}</prop> </props> </property> </bean> <!– HikariCP configuration –> <bean … Read more

Spring JDBC Template for calling Stored Procedures

There are a number of ways to call stored procedures in Spring. If you use CallableStatementCreator to declare parameters, you will be using Java’s standard interface of CallableStatement, i.e register out parameters and set them separately. Using SqlParameter abstraction will make your code cleaner. I recommend you looking at SimpleJdbcCall. It may be used like … Read more

Error when connect to impala with JDBC under kerberos authrication

Forget about the Hadoop UGI: a JDBC driver just needs the raw JAAS configuration to create a Kerberos ticket on-the-fly (with useKeyTab raised and useTicketCache lowered). System properties java.security.krb5.conf => (optional) non-defaut Kerberos conf java.security.auth.login.config => JAAS config file javax.security.auth.useSubjectCredsOnly => must be forced to “false” (the default has changed in some Java release, duh) … Read more

How to execute IN() SQL queries with Spring’s JDBCTemplate effectively?

You want a parameter source: Set<Integer> ids = …; MapSqlParameterSource parameters = new MapSqlParameterSource(); parameters.addValue(“ids”, ids); List<Foo> foo = getJdbcTemplate().query(“SELECT * FROM foo WHERE a IN (:ids)”, parameters, getRowMapper()); This only works if getJdbcTemplate() returns an instance of type NamedParameterJdbcTemplate