Spring JdbcTemplate example to get a large ResultSet and process it. P.S Tested with Java 8 and Spring JDBC 5.1.4.RELEASE 1. Get large ResultSet 1.1 Below is a classic findAll to get all data from a table. BookRepository.java public List<Book> findAll() { return jdbcTemplate.query( "select * from books", (rs, rowNum) -> new Book( rs.getLong("id"), rs.getString("name"), […]
In this tutorial, we will show you how to use Spring Boot JDBC SimpleJdbcCall to call a stored procedure and stored function from a Oracle database. Technologies used : Spring Boot 2.1.2.RELEASE Spring JDBC 5.1.4.RELEASE Oracle database 19c HikariCP 3.2.0 Maven 3 Java 8 Unlike JdbcTemplate, Spring Boot didn’t create any SimpleJdbcCall automatically, we have […]
In this tutorial, we will show you how to use Spring Boot JDBC JdbcTemplate and NamedParameterJdbcTemplate. Technologies used : Spring Boot 2.1.2.RELEASE Spring JDBC 5.1.4.RELEASE HikariCP 3.2.0 H2 in-memory database 1.4.197 Maven 3 Java 8 In Spring Boot JDBC, the database related beans like DataSource, JdbcTemplate and NamedParameterJdbcTemplate will be configured and created during the […]
Making a JDBC connection to the MySQL server with the latest mysql-connector-java:8.0.16 and hits the following SQLException: Tested with MySQL 5.7 Java 8 JDBC driver, mysql-connector-java 8.0.16 try (Connection conn = DriverManager.getConnection( "jdbc:mysql://127.0.0.1:3306/test", "root", "password")) { //… } catch (Exception e) { e.printStackTrace(); } Output java.sql.SQLException: The server time zone value ‘Malay Peninsula Standard Time’ […]
Ordinal binding or index binding: String name = stat.getString(2); BigDecimal salary = stat.getBigDecimal(3); Timestamp createdDate = stat.getTimestamp(4); Named binding: String name = stat.getString("NAME"); BigDecimal salary = stat.getBigDecimal("SALARY"); Timestamp createdDate = stat.getTimestamp("CREATED_DATE"); If we mixed both like this: String name = stat.getString(2); BigDecimal salary = stat.getBigDecimal("SALARY"); Timestamp createdDate = stat.getTimestamp(4); Error: java.sql.SQLException: operation not allowed: Ordinal […]
This is caused by the requested SID doesn’t exist in {ORACLE_HOME}/network/admin/tnsnames.ora P.S Tested with Oracle database 19c with ojdbc8.jar 1. JDBC try (Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "system", "password")) { //… } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } Output: SQL State: 66000 Listener refused the connection with the following […]
A JDBC CallableStatement example to show you how to call a stored function from PostgreSQL database. P.S Tested with PostgreSQL 11 and Java 8 pom.xml <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.2.5</version> </dependency> 1. Call Function 1.1 Create a stored function and calling it via JDBC. FunctionReturnString.java package com.mkyong.jdbc.callablestatement; import java.sql.*; public class FunctionReturnString { public static void […]
Since Java 1.6, JDBC 4.0 API, it provides a new feature to discover java.sql.Driver automatically, it means the Class.forName is no longer required. Just put any JDBC 4.x driver in the project classpath, and Java is able to detect it. For example, JDBC driver for PostgreSQL: pom.xml <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.2.5</version> </dependency> And it works: […]
A JDBC example to connect to a PostgreSQL, and print out all the tables from the default database postgres pom.xml <dependencies> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.2.5</version> </dependency> </dependencies> P.S Tested with Java 8 and postgresql jdbc driver 42.2.5 PrintAllTables.java package com.mkyong.jdbc; import java.sql.*; public class PrintAllTables { public static void main(String[] argv) { System.out.println("PostgreSQL JDBC Connection […]
Java JDBC PreparedStatement example to create a SQL IN condition. 1. PreparedStatement + Array In JDBC, we can use createArrayOf to create a PreparedStatement IN query. @Override public List<Integer> getPostIdByTagId(List<Integer> tagIds) { List<Integer> result = new ArrayList<>(); String sql = "SELECT tr.object_id as post_id FROM wp_term_relationships tr " + " JOIN wp_term_taxonomy tt JOIN wp_terms […]
After some SQL queries, the system is unable to get any connection from the HikariPool and prompts the following error message HikariPool-1 – Connection is not available, request timed out after 30002ms. Tested with HikariCP 3.3.1 mysql-connector-java 5.1.47 Java 8 1. Solution 1.1 Enable the debug logs for com.zaxxer.hikari, it will print out many useful […]
In this article, we will show you how to create a Spring Boot JDBC application + Oracle database + Commons DBCP2 connection pool. Tools used in this article : Spring Boot 1.5.1.RELEASE Oracle database 11g express Oracle JDBC driver ojdbc7.jar Commons DBCP2 2.1.1 Maven Java 8 Note Related – Spring Boot JDBC + MySQL + […]
Start a Spring Boot application and making a JDBC connection, hits the following warning messages on console : Fri Feb 10 18:43:02 SGT 2017 WARN: Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. […]
In this article, we will show you how to create a Spring Boot JDBC application + MySQL and HikariCP. Tools used in this article : Spring Boot 1.5.1.RELEASE MySQL 5.7.x HikariCP 2.6 Maven Java 8 Note Related – Spring Boot JDBC + Oracle database + Commons DBCP2 example 1. Project Structure A standard Maven project […]
In this tutorial, I will show you how to run a MySQL script file using ibatis ScriptRunner class. First, download the ibatis and Mysql JDBC Driver, and add the jar files into your classpath. Now, run below code. It will execute a script.sql file. RunSqlScript.java package com.mkyong; import java.io.BufferedReader; import java.io.FileReader; import java.io.Reader; import java.sql.Connection; […]
This article shows you how to use try-with-resources in Java. Table of contents: 1 Java try-with-resources before and after 2. try-with-resources with single resource 3. try-with-resources with multiple resources 3.1 JDBC example. 4. Custom resource with AutoCloseable interface 5. Resource open and closing order 6. Java 9 – final or effectively final variables Download Source […]
Here’s a simple guide to show you how to add an Oracle JDBC driver into your Maven local repository, and also how to reference it in pom.xml Tested with Oracle database 19c and Java 8 Note Due to Oracle license restrictions, the Oracle JDBC driver is not available in the public Maven repository. To use […]
JDBC tutorial with full example, including CRUD sql statement with JDBC Statement and PreparedStatement, interact Stored Procedure with CallableStatement, JDBC transaction and how to integrate with Spring and JSF 2.0
JDBC transaction make sure a set of SQL statements is executed as a unit, either all of the statements are executed successfully, or NONE of the statements are executed (rolled back all changes). 1. Without JDBC Transaction 1.1 A JDBC example to insert two rows and update one row. TransactionExample.java package com.mkyong.jdbc; import java.math.BigDecimal; import […]
A JDBC Statement example to send a batch of SQL commands (drop,create, insert, update) to the database. BatchUpdate.java package com.mkyong.jdbc.statement; import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.time.LocalDateTime; import java.util.Arrays; public class BatchUpdate { public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password"); Statement statement = […]
A JDBC PreparedStatement example to send a batch of SQL commands (create, insert, update) to the database. BatchUpdate.java package com.mkyong.jdbc.preparestatement; import java.math.BigDecimal; import java.sql.*; import java.time.LocalDateTime; import java.util.Arrays; public class BatchUpdate { public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password"); PreparedStatement psDDL = conn.prepareStatement(SQL_CREATE); PreparedStatement psInsert = conn.prepareStatement(SQL_INSERT); PreparedStatement […]
A JDBC CallableStatement example to call a stored procedure which accepts IN and OUT parameters. Tested with Java 8 and Oracle database 19c pom.xml <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc</artifactId> <version>8</version> <scope>system</scope> <systemPath>path.to/ojdbc8.jar</systemPath> </dependency> 1. JDBC CallableStatement 1.1 A PL/SQL stored procedure which accepts IN and OUT parameters. CREATE OR REPLACE PROCEDURE get_employee_by_id( p_id IN EMPLOYEE.ID%TYPE, o_name OUT […]
A JDBC CallableStatement example to call a stored procedure which returns a cursor. Tested with Java 8 and Oracle database 19c pom.xml <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc</artifactId> <version>8</version> <scope>system</scope> <systemPath>path.to/ojdbc8.jar</systemPath> </dependency> 1. JDBC CallableStatement 1.1 A PL/SQL stored procedure which returns a cursor. CREATE OR REPLACE PROCEDURE get_employee_by_name( p_name IN EMPLOYEE.NAME%TYPE, o_c_dbuser OUT SYS_REFCURSOR) AS BEGIN OPEN […]
A JDBC CallableStatement example to call a stored procedure which accepts IN parameters. Tested with Java 8 and Oracle database 19c pom.xml <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc</artifactId> <version>8</version> <scope>system</scope> <systemPath>path.to/ojdbc8.jar</systemPath> </dependency> 1. JDBC CallableStatement 1.1 A PL/SQL stored procedure to insert a row. CREATE OR REPLACE PROCEDURE insert_employee( p_name IN EMPLOYEE.NAME%TYPE, p_salary IN EMPLOYEE.SALARY%TYPE, p_date IN EMPLOYEE.CREATED_DATE%TYPE) […]
Problem A simple table script in Oracle database. CREATE TABLE DBUSER ( USER_ID NUMBER (5) NOT NULL, USERNAME VARCHAR2 (20) NOT NULL, CREATED_BY VARCHAR2 (20) NOT NULL, CREATED_DATE DATE NOT NULL, PRIMARY KEY ( USER_ID ) ) No idea how to insert current date value, e.g. “04/04/2011” into “CREATED_DATE” field, via JDBC PreparedStatement. String insertTableSQL […]
A JDBC PreparedStatement example to delete a row. RowDelete.java package com.mkyong.jdbc.preparestatement.row; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class RowDelete { private static final String SQL_DELETE = "DELETE FROM EMPLOYEE WHERE NAME=?"; public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password"); PreparedStatement preparedStatement = conn.prepareStatement(SQL_DELETE)) { preparedStatement.setString(1, "mkyong"); […]
A JDBC PreparedStatement example to select a list of rows from the database. RowSelect.java package com.mkyong.jdbc.preparestatement.row; import com.mkyong.jdbc.model.Employee; import java.math.BigDecimal; import java.sql.*; public class RowSelect { private static final String SQL_SELECT = "SELECT * FROM EMPLOYEE"; public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password"); PreparedStatement preparedStatement = conn.prepareStatement(SQL_SELECT)) { […]
Problem A simple table script in Oracle database. CREATE TABLE DBUSER ( USER_ID NUMBER (5) NOT NULL, USERNAME VARCHAR2 (20) NOT NULL, CREATED_BY VARCHAR2 (20) NOT NULL, CREATED_DATE DATE NOT NULL, PRIMARY KEY ( USER_ID ) ) No idea how to insert a timestamp value, e.g. “04/04/2011 14:45:04” into “CREATED_DATE” field, via JDBC PreparedStatement. String […]
A JDBC PreparedStatement example to update a row. RowUpdate.java package com.mkyong.jdbc.preparestatement.row; import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class RowUpdate { private static final String SQL_UPDATE = "UPDATE EMPLOYEE SET SALARY=? WHERE NAME=?"; public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password"); PreparedStatement preparedStatement = conn.prepareStatement(SQL_UPDATE)) […]
A JDBC PreparedStatement example to insert a row into the database. RowInsert.java package com.mkyong.jdbc.preparestatement.row; import java.math.BigDecimal; import java.sql.*; import java.time.LocalDateTime; public class RowInsert { private static final String SQL_INSERT = "INSERT INTO EMPLOYEE (NAME, SALARY, CREATED_DATE) VALUES (?,?,?)"; public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password"); PreparedStatement preparedStatement = […]
A JDBC PreparedStatement example to create a table in the database. CREATE TABLE EMPLOYEE ( ID serial, NAME varchar(100) NOT NULL, SALARY numeric(15, 2) NOT NULL, CREATED_DATE timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP PRIMARY KEY (ID) ); TableCreate.java package com.mkyong.jdbc.preparestatement.table; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class TableCreate { private […]
A JDBC Statement example to select a list of rows from the database. RowSelect.java package com.mkyong.jdbc.statement.row; import com.mkyong.jdbc.model.Employee; import java.math.BigDecimal; import java.sql.*; public class RowSelect { public static void main(String[] args) { String sql = "SELECT * FROM EMPLOYEE"; try (Connection conn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password"); Statement statement = conn.createStatement()) { ResultSet resultSet = […]
A JDBC Statement example to delete a row. RowDelete.java package com.mkyong.jdbc.statement.row; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class RowDelete { public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password"); Statement statement = conn.createStatement()) { int row = statement.executeUpdate(deleteByName("mkyong")); // rows affected System.out.println(row); } catch (SQLException e) { […]
A JDBC Statement example to update a row. RowUpdate.java package com.mkyong.jdbc.statement.row; import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class RowUpdate { public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password"); Statement statement = conn.createStatement()) { int row = statement.executeUpdate(updateSalaryByName("mkyong", new BigDecimal(1080))); // rows affected System.out.println(row); } […]
A JDBC Statement example to insert a row into the database. RowInsert.java package com.mkyong.jdbc.statement.row; import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.time.LocalDateTime; public class RowInsert { public static void main(String[] args) { // auto close connection and statement try (Connection conn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password"); Statement statement = conn.createStatement()) { […]
A JDBC Statement example to issue a create table SQL to the database. CREATE TABLE EMPLOYEE ( ID serial, NAME varchar(100) NOT NULL, SALARY numeric(15, 2) NOT NULL, CREATED_DATE timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP PRIMARY KEY (ID) ); TableCreate.java package com.mkyong.jdbc.statement.table; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class TableCreate […]
A JDBC example to show you how to connect to a Oracle database with a JDBC driver. Tested with: Java 8 Oracle database 19c Oracle JDBC driver for Java 8, ojdbc8.jar 1. Download Oracle JDBC Driver Visit Oracle database website and download the Oracle JDBC Driver. 2. JDBC Connection Note Find your Oracle SID in […]
Here’s a guide to show you how to integrate JSF 2.0 with database via JDBC. In this example, we are using MySQL database and Tomcat web container. Directory structure of this example 1. Table Structure Create a “customer” table and insert five dummy records. Later, display it via JSF h:dataTable. SQL commands DROP TABLE IF […]
Problem JSF 2.0 web application, a managed bean uses @Resource to inject the “jdbc/mkyongdb” datasource into a ds property. @ManagedBean(name="customer") @SessionScoped public class CustomerBean implements Serializable{ //resource injection @Resource(name="jdbc/mkyongdb") private DataSource ds; When deployed to Tomcat 6, it hits following error messages for the MySQL datasource configuration. com.sun.faces.mgbean.ManagedBeanCreationException: An error occurred performing resource injection on […]
In JdbcTemplate, SQL parameters are represented by a special placeholder “?” symbol and bind it by position. The problem is whenever the order of parameter is changed, you have to change the parameters bindings as well, it’s error prone and cumbersome to maintain it. To fix it, you can use “Named Parameter“, whereas SQL parameters […]
