Database Systems - Java Database Programming with MySQL and Connector/J - Using Query Parameters

Connector/J is a MySQL component based on the JDBC standard from Oracle
Setup your Java program for database programming:
Import the Connector/J jar file into your Java project and configure your classpath
Import java.sql.Connection and java.sql.DriverManager
Connect with DriverManager.getConnection()within a try-catch statement
Close the connection with connection.close()

Connector/J Link: https://dev.mysql.com/downloads/connector/j/

Executing queries

createStatement() – creates a Statement object that is associated to the database connection. Statement is an interface used for executing SQL queries. Import it in your Java code with import java.sql.Statement;.

executeQuery() - statement method that compiles the SELECT query into low-level databse operations and then executes the query. Pass the string query as the parameter, and it returns a ResultSet object which contains the results of the query

executeUpdate() - executes queries that update data such as INSERT, UPDATE, and DELETE. It returns the number of matched query rows.

execute() - executes any SQL statements, and can even be used for CREATE TABLE statements. It returns a boolean value true if a ResultSet object is generated.

getResultSet() – accesses the ResultSet generated by the execute() method.

close() - closes and releases statement object. Call this method before you close any database connection.

Fetching values

The ResultSet interface gets query results.

executeQuery() - returns a ResultSet object with the query results. Each ResultSet object maintains a pointer to the current row of the query result. The first row is the initial row, and the next() method movies it to the next row and also returns true if the current row advances, or false if you’re at the last row of the query result.

ResultSet methods can access or fetch values from the result set:

getInt() takes a column name parameter and returns the integer value of the column.
getDouble() takes a column name parameter and returns the Double value of the column.
getString() takes a column name parameter and returns the String value of the column.
Other ResultSet methods return additional information:
getMetaData() returns a ResultSetMetaData object, containing information about the columns of a ResultSet object.
getWarnings() returns the first warning generated by a query.
The Result interface must be imported to a Java program with import java.sql.ResultSet;.

Query parameters

PreparedStatement - interface that extends the Statement interface and adds support for SQL query parameters.

prepareStatement() – creates PreparedStatment objects with the Connection interface, by passing a SQL query String parameter.

PreparedStatement inherits the executeQuery() and executeUpdate() methods from Statement, but they do not take a query parameter because prepared statements are assigned a query when they are created.

The SQL query can contains one or more ? characters to represent query parameters. Use PreparedStatement methods such as setString() and setInt() assign values to placeholders.

Ex: preparedStatement.setString(1, movieID) sets the first ? in the preparedStatement object to the String value of movieID.

It’s better to use placeholders with PreparedStatement rather than using Statement, because it could prevent a SQL injection attack, where a hacker can try to change the values of the SQL statements.



Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!