You can also use a PreparedStatement object to execute batch updates. The PreparedStatement enables you to reuse the same SQL statement, and just insert new parameters into it, for each update to execute. This method also avoids SQL Injection issue.
Here is an example (Batch Update using PreparedStatement):
SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warning simply alerts the user when something did not happen as planned. A warning can be reported on a Connection, ResultSet and Statement object. Each of these classes has a getWarnings() method, which we must invoke in order to see the warning reported on the calling object.
The below are the MYSQL script, which takes a backup/copy of the entire table into a temp table. This makes sure that you have a full backup of the data and table format, These query will be quiet useful in case something goes wrong.
You can use either of these queries to create a backup.
CREATE TABLE employees_backup AS ( SELECT * from employees);
CREATE TABLE SCHEMA.employees_backup LIKE SCHEMA.employees;
INSERT SCHEMA.employees_backup SELECT * FROM SCHEMA.employees;
JDBC is a Java API that enables Java application to connect and execute query to the database. JDBC API uses jdbc drivers to connect to the database.
This tutorial will not cover every single detail of the JDBC API, but focus on the most commonly used features. Here you will learn many step by step examples on using JDBC Statement, PreparedStatement, CallableStatement, and JDBC Transaction.
The rest you can read about in the JavaDoc afterwards.
Happy learning :-)
Some quick guides to show how JDBC interact with databases like MySQL, Oracle and PostgreSQL.
SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker) – Wikipedia
SQL injection errors occur when:
1. Data enters a program from an untrusted source.
2. The data is used to dynamically construct a SQL query.
Now, let us see some of the practical Scenario of SQL injection. For example consider an application which has a login page, it is possible that the application uses a dynamic SQL Statement as below.
SELECT * FROM Employee WHERE Employee_Name = 'strEmployeeName' AND Password = 'strPassword';
This statement is expected to return at least a single row with the employee details from the Employee table as the result set when there is a row with the employee name and password entered in the SQL statement.
If the attacker would enter Gokul as the strEmployeeName(in the textbox for employee name) and Krish as strPassword (in the textbox for password), the above SQL statement would become:
SELECT * FROM Employee WHERE Employee_Name = 'Gokul' AND Password = 'Krish';
If an attacker enters the string ‘Gokul’ OR ‘a’=’a’ for strEmployeeName, and ‘Krish’ OR ‘a’=’a’ for strPassword then the query becomes the following:
SELECT * FROM Employee WHERE Employee_Name = 'Gokul' OR 'a'='a'
AND Password = 'Krish' OR 'a'='a';
Since ‘a’=’a’ condition is always true, the result set would consist of all the rows in the Employee table. The application could allow the attacker to log in as the first employee in the Employee table.
If the attacker would enter ‘Gokul’; DROP table Employee; as strEmployeeName and anything as strPassword, the SQL statement would become like the one below.
SELECT * FROM Employee WHERE Employee_Name = 'Gokul';
DROP table Employee; AND Password = 'Krish'
This statement could cause the table Employee to be permanently deleted from the database.
So inorder to avoid SQL inject errors it is better to use prepare statement instead of normal statement.
When we are inserting a record into the database table and the primary key is an auto-increment or auto-generated key, then the insert query will generate it dynamically. The below example shows how to get this key after insert statement.
After perfoming executeUpdate() method on PreparedStatement, call getGeneratedKeys() method on PreparedStatement.
The getGeneratedKeys() provide a standard way to make auto-generated or identity column values available to an application that is updating a database table without a requiring a query and a second round-trip to the server. SQL Server allows only a single auto increment column per table.
The ResultSet that is returned by getGeneratedKeys method will have only one column, with the returned column name of GENERATED_KEYS.
If generated keys are requested on a table that has no auto increment column, the JDBC driver will return a null result set.
When you insert rows by executeUpdate or execute an INSERT statement or an INSERT within SELECT statement, you need to indicate that you will want to retrieve automatically generated key values. You do that by setting a flag in a Connection.prepareStatement, Statement.executeUpdate, or Statement.execute method call. The statement that is executed must be an INSERT statement or an INSERT within SELECT statement, Otherwise, the JDBC driver ignores the parameter that sets the flag.