In Java , JDBC (Java DataBase
Connectivity) API is used for connecting to relational databases from clients. This
JDBC API uses Drivers (Java library files) and Connection Strings/URLs to
connect to the databases.
How do you connect to SQL server to execute some queries. See the code below to have an idea.
DataSource object to get a connection
public
Connection getConnection() throws SQLException {
Properties connectionProps
= new Properties();
connectionProps.put("user",
this.userName);
connectionProps.put("password",
this.password);
String url =
"jdbc:sqlserver://localhost:3306/database"; //format of MySQL
connection string
// Connection
con = datasource.getConnection(username, password); OR
Connection con
= DriverManager.getConnection(url, "username", "password");
System.out.println("Connected to
database");
return con;
}
public static void viewTable(Connection con, String dbName)
throws SQLException {
Statement stmt = null;
String query =
"select NAME, ID, PRICE, " +
"SALES, TOTAL " +
"from " + dbName + ".COFFEES";
try {
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String coffeeName = rs.getString("NAME");
int supplierID =
rs.getInt("ID");
float price =
rs.getFloat("PRICE");
int sales =
rs.getInt("SALES");
int total =
rs.getInt("TOTAL");
rs.moveToInsertRow();
rs.updateString("NAME",
coffeeName);
rs.updateInt("ID", supplierID);
rs.updateFloat("PRICE",
price);
rs.updateInt("SALES", sales);
rs.updateInt("TOTAL", total);
rs.insertRow();
rs.beforeFirst();
System.out.println(coffeeName + "\t" + supplierID +
"\t" + price +
"\t" + sales +
"\t" + total);
}
} catch (SQLException e ) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmt != null) { stmt.close(); }
}
}
Classes and Methods used
DriverManager.getConnection(url, "username", "password")
datasource.getConnection(username, password);
connection.createStatement();
connection.close()
statement.executeQuery(query);
ResultSet.next()
ResultSet.getString("NAME");
ResultSet.getInt("SALES");
ResultSet.getFloat("PRICE");
ResultSet.moveToInsertRow();
ResultSet.insertRow();
ResultSet.beforeFirst();
ResultSet.updateString("NAME",
coffeeName);
ResultSet.updateInt("ID",
supplierID);
ResultSet.updateFloat("PRICE",
price);
MySQL
try{
Class.forName("com.mysql.jdbc.Driver").newInstance(); //using MySQL
JDBC Driver
String url =
"jdbc:mysql://hostname:port/database"; //format of MySQL connection
string
conn = DriverManager.getConnection(url,
"username", "password");
} catch (Exception e){...}
finally {
conn.close();
}
Comments