How to Use SQL With Java
Java programmers frequently need to develop applications that are connected to a database. In such applications, Structured Query Language (SQL) is used to obtain data from the database. SQL commands can be issued from a Java application using the JDBC library, which is included by default in every Java Development environment. The JDBC library takes care of many low-level details of database connections, leaving Java programmers free to focus on implementing business logic in their applications.
Other People Are Reading
Instructions
-
-
1
Click on the "Start" button from the Windows task bar and type "Notepad" from the search box. Select "Notepad" from the list of applications that appear.
-
2
Create a new Java class. This step varies depending on the class name that you require. For example, the following lines of code show the creation of a Bicycle class:
public class Bicycle {
}
-
-
3
Add the following lines inside the class:
public Connection getConnection() throws SQLException {
Connection conn = null;
Properties connectionProps = new Properties();
connectionProps.put("user", this.userName);
connectionProps.put("password", this.password);
if (this.dbms.equals("mysql")) {
conn = DriverManager.
getConnection("jdbc:" + this.dbms + "://" + this.serverName +
":" + this.portNumber + "/", connectionProps);
}
System.out.println("Connected to database");
return conn;
}
This creates a connection to a MySQL database using the specified user name and password.
-
4
Type the following lines of code inside the class:
public static void viewTable(Connection con, String dbName) throws SQLException {
}
This creates a new method called viewTable that accepts the connection created previously and produces an SQL exception.
-
5
Add the following lines of code inside the "viewTable()" method:
Statement stmt = null;
String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from " + dbName + ".COFFEES";
try {
stmt = con.createStatement();
} catch (SQLException e ) {
JDBCTutorialUtilities.printSQLException(e);
}
This creates a statement based on the specified query from the established connection.
-
6
Append the following codes inside the try block:
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String coffeeName = rs.getString("COF_NAME");
int supplierID = rs.getInt("SUP_ID");
float price = rs.getFloat("PRICE");
int sales = rs.getInt("SALES");
int total = rs.getInt("TOTAL");
}
This executes the query and retrieves the result through a ResultSet object.
-
7
Type the following lines of codes at the end of the catch block:
} finally {
if (stmt != null) { stmt.close(); }
}
This closes the connection when it is no longer required.
-
1
Tips & Warnings
It is important to ensure that the connection is closed in the finally block. Failing to do so can possibly result in a resource leak.
References
- Photo Credit Thinkstock Images/Comstock/Getty Images
