Practice JDBC API using Oracle SQL
Overview
In this project, we practiced JDBC API by running DDL and DML commands using static SQL, prepared statement, or updatable result set. The commands include INSERT, UPDATE, DELETE, CREATE, DROP, etc.
query package
Get connection to the Oracle database, querying the Country table to show the results which life expectancy is in a range. Use prepareStatement to ask user to input two values, and a static statement for the two fixed values.
unknown package
The code allows users to input and run SQL syntax (select, update, delete, insert) freely in the console. The code will first acquire an input/query from the user, and then determine what type of query it is by trying to run it.
If the query is valid and is either UPDATE, DELETE, or INSERT, then the program will run the syntax and print a successful message. If the query is a select statement, then the program will grab the returned column names and use the names to get row data and print the result row by row.
update package
Connection to the database is made successfully and then the country table is dropped and then created. UpdateTablePreparedStm, UpdateTableStaticSQL and UpdateTableUpdateResultSet classes are used to perform insert, update and delete SQL operations on the country table records by implementing prepared statement, static SQL and result set respectively.
Github
Project Walk-Through
AnySQL.java (Part 1)
This part of the code tries executing the user input and determines whether the input is a SELECT query, UPDATE/DELETE/INSERT query, or not a valid SQL query, and then provides the corresponding output to the console.
AnySQL.java (Part 2)
If the user input is a valid SELECT command, the program will output the query result to the console as a table.
JDBCUtilities.java (Database Connection)
This utility class is used to establish a connection through JDBC API to the Oracle database.
Console / Terminal Demonstration
This is where a user interacts with the program. The user can freely input anything, and the program will check the validity of the input.
If the input is a valid SQL command, then the program will execute the command and return a result. However, if the input is not a valid SQL command, the program will show a warning message to the user as demonstrated in the screenshot.