jdbc

java.sql.SQLException: The server time zone value ‘xx time’ is unrecognized

by

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’ …

Read more

java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!

by

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 …

Read more

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

by

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 …

Read more

JDBC CallableStatement – PostgreSQL Stored Function

by

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 …

Read more

JDBC Class.forName() is no longer required

by

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: …

Read more

JDBC – How to print all table names from a database?

by

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 …

Read more

JDBC PreparedStatement SQL IN condition

by

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 …

Read more

HikariPool-1 – Connection is not available, request timed out after 30002ms.

by

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 …

Read more

How to run a MySQL Script using Java

by

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; …

Read more

JDBC Transaction example

by

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 …

Read more

JDBC Statement – Batch Update

by

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 = …

Read more

JDBC PreparedStatement – Batch Update

by

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 …

Read more

JDBC CallableStatement – Stored Procedure OUT parameter example

by

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 …

Read more

JDBC CallableStatement – Stored Procedure CURSOR example

by

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 …

Read more

JDBC CallableStatement – Stored Procedure IN parameter example

by

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) …

Read more

Insert date value in PreparedStatement

by

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 …

Read more

JDBC PreparedStatement – Delete a row

by

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"); …

Read more

JDBC PreparedStatement – Select list of rows

by

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)) { …

Read more

Insert timestamp value in PreparedStatement

by

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 …

Read more

JDBC PreparedStatement – Update a row

by

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)) …

Read more

JDBC PreparedStatement – Insert a row

by

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 = …

Read more

JDBC PreparedStatement – Create a table

by

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 …

Read more

JDBC Statement – Select list of rows

by

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 = …

Read more

JDBC Statement – Delete a row

by

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) { …

Read more

JDBC Statement – Update a row

by

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); } …

Read more

JDBC Statement – Insert a row

by

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()) { …

Read more

JDBC Statement – Create a table

by

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 …

Read more

Connect to Oracle DB via JDBC driver

by

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 …

Read more

Connect to MySQL with JDBC driver

by

A JDBC example to show you how to connect to a MySQL database with a JDBC driver. Tested with: Java 8 MySQL 5.7 MySQL JDBC driver mysql-connector-java:8.0.16 1. Download MySQL JDBC Driver Visit https://dev.mysql.com/downloads/connector/j/ to download the latest MySQL JDBC Driver. 2. JDBC Connection 2.1 Make a connection to the MySQL database. JDBCExample.java import java.sql.Connection; …

Read more

Connect to PostgreSQL with JDBC driver

by

A JDBC example to show you how to connect to a PostgreSQL database with a JDBC driver. Tested with: Java 8 PostgreSQL 11 PostgreSQL JDBC driver 42.2.5 1. Download PostgreSQL JDBC Driver Visit http://jdbc.postgresql.org/download.html to download the latest PostgreSQL JDBC Driver. 2. JDBC Connection 2.1 Make a connection to the PostgreSQL database. JDBCExample.java import java.sql.Connection; …

Read more