The Java Database Connectivity (JDBC) API enables Java application to interact with database.
1. Getting Started
2. Statement
This Statement has no cache, good for simple and static SQL statements like CREATE or DROP. In Statement, the way we construct the condition or parameters in SQL is prone to SQL injection, remember escape the quotes and special characters.
statement.execute(sql)– Normally for DDL like CREATE or DROPstatement.executeUpdate(sql)– Normally for DML like INSERT, UPDATE, DELETEstatement.executeQuery(sql)– Run SELECT query and return aResultSetstatement.executeBatch()– Run SQL commands as a batch
Articles:
- JDBC Statement – Create a table
- JDBC Statement – Insert a row
- JDBC Statement – Update a row
- JDBC Statement – Delete a row
- JDBC Statement – Select list of rows
- JDBC Statement – Batch Update
3. PreparedStatement
PreparedStatement extends Statement to provide better performance by precompiled and cached the SQL statement, good for SQL statement that need to execute multiple times. Furthermore, it provides many setXxx() to protect SQL injection by escaping the quotes and special characters.
preparedStatement.execute()– Normally for DDL like CREATE or DROPpreparedStatement.executeUpdate()– Normally for DML like INSERT, UPDATE, DELETEpreparedStatement.executeQuery()– Run SELECT query and return aResultSetpreparedStatement.executeBatch()– Run SQL commands as a batch
Articles:
- JDBC PreparedStatement – Create a table
- JDBC PreparedStatement – Insert a row
- JDBC PreparedStatement – Update a row
- JDBC PreparedStatement – Delete a row
- JDBC PreparedStatement – Select list of rows
- JDBC PreparedStatement – Batch Update
- JDBC PreparedStatement SQL IN condition
4. CallableStatement
CallableStatement extends PreparedStatement, for executing stored procedures or functions from the database.
conn.prepareCall(sql)
Oracle database
- JDBC CallableStatement – Stored Procedure IN parameter example
- JDBC CallableStatement – Stored Procedure OUT parameter example
- JDBC CallableStatement – Stored Procedure CURSOR example
PostgreSQL
5. Transaction
conn.setAutoCommit(false); // default true
// start transaction block
// SQL statements
// end transaction block
conn.commit();
conn.setAutoCommit(true);
6. Spring JDBC Database Access
JdbcTemplate examples.
- Spring Boot JDBC Examples
- Spring Boot JDBC Stored Procedure Examples
- Spring JdbcTemplate Querying Examples
- Spring JdbcTemplate Handle Large ResultSet
- Spring JdbcTemplate batchUpdate() Example
- Spring Boot JDBC Image BLOB Examples
FAQs
- How to add Oracle JDBC driver in your Maven local repository
- JDBC – How to print all table names from a database?
- JDBC Class.forName() is no longer required
- Oracle – ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
- java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!
- java.sql.SQLException: The server time zone value ‘xx time’ is unrecognized
References
- Oracle – JDBC tutorials
- SQL in Java 8: ResultSet Streams
- Wikipedia – Java Database Connectivity
- Wikipedia – SQL injection
- Statement JavaDocs
- PreparedStatement JavaDocs
- CallableStatement JavaDocs
- java.sql summary JaavDocs
Oracle
MySQL
PostgreSQL
Thank you very much for this valuable information.
Thank you very much :)))
You’re my favorite person in the world!
sir how to compare two images in java
Thank You for very useful tutorial
I have question related to JDBC can you help me with it ?
I want to draw bezier cubic curves by retrieving data from MySQL
database and then display the shape on JFrame I have table has 3 columns
(ID, ObjectID, Points) Points column is coordinates of cubic curves the statement is
SELECT * FROM DB.TABNAME WHERE OBJECTID=1;
ObjectID=1 has 11 ID’s, each ID contains a shape if i combine all ID`s together will form composite shape
How can i retrieve data from MySQL database, that data are path to draw bezier cubic curves in java GUI?
hi mkyong, its really awesome, a very good site to learn……
thnq u….
hi yong
Can you tell me how to get list of database in Oracle as well as MySql using java?
Like SQLYog IDE which lists all databases in MySql.
I want the same thing. I want to connect to any Database server and fetch list of database in it.
thansk for u share sir,
nice post
i want subscription with jdbc code pls help
Hi Mr mkyong, your post’s very awesome and really helpful
Hello
I’m creating a web service on Java. And I need it to connect with many dbms (Oracle, MySql…). Is there a way to do that ?
PS :I’m working with hibernate.
i want all database data retrive in the xml file code
i want jdbc with xml programs…..
where is the search box in this website?
where is the search box inthis site?
all the tutorials are very helpful for me and all others
can u upload tutorials for servlets ans jsp
thank you mr.yong
Good article
hi sir,could help me please,actually we are using prepared statement for speed up and much more,ok but i want to perform single operation not bulk,which one is better and fast general or prepared stament(and also in terms of sql injection).thanx a lot sir.
thanks for u tutorial
i like this posting
Hi mkyong, very good tutorials! Thaks a lot for making java so simple =)
Can you make a tutorial about using trigger??
hi sir,
can u explain me, how can we get database table address in java program., pls help me out sir, this question asked in interview for my friend.
thnx mr. yong this article of your’s abt JDBC helped me a lot
thnx mr. yong this article of your’s abt JDBC helped me a lot
Hi Yong,
can you please suggest a good JSP tutorial?
thanks.
nice article…….. keep it up,very helpful.
an article in JFreeChart and JavaMail 🙂 plz
Very nice article
An article about deadlocks would be appreciated.