Hello! Today I want to make a couple of examples of database access using JDBC within Spring framework. In this article we'll see how DB's manipulations can be simplified using jdbcTemplate and JdbcDaoSupport.
Cute image from me:
Why? I really don't understand, this solution is opposite to the one of OOP design principle (DRY: Don't Repeat Yourself). If you use some code more then ones - then you must detach it to a separate method and invoke it every time you need in the future.
Spring developers offer us to avoid repeating yourself and use jdbcTemplate which can do all redundant work for us.
Let's see the differences!
(I'll give you a general example):
Without jdbcTemplate:
public class JDBCExampleDAO implements ExampleDAO {
private DataSource dataSource;
public void setDataSource(DataSource dataSource) { this.dataSource = dataSource;
}
public void updateTable(String key, String someValue) throws SQLException {
Connection dbConnection = null;
PreparedStatement preparedStatement = null;
final String updateTableSQL = "UPDATE table_name SET column_name = ? "
+ " WHERE key = ?";
try {
dbConnection = getDBConnection();
preparedStatement = dbConnection.prepareStatement(updateTableSQL);
preparedStatement.setString(1, someValue);
preparedStatement.setInt(2, key);
preparedStatement.executeUpdate();
} catch (SQLException e) {
// handle exception
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
Using JdbcDaoSupport we only need to extend JdbcDaoSupport and after that we can get jdbcTemplate using getJdbcTemplate() method:
public class JDBCExampleDAO extends JdbcDaoSupport implements ExampleDAO {
public void updateTable(String key, String someValue) {
String sql = "UPDATE table_name " + "SET column_name = ?"
+ " WHERE key = ?";
getJdbcTemplate().update(sql, new Object[]{someValue, key});
} Wow! this is all! It is enough! Are you enjoy? I definitely like the the second solution much more!
We must declare some additional beans to have JdbcDaoSupport avalable:
In XML configuration:
...
<bean id="dataSource"
class="[your_file_path].DriverManagerDataSource">
//Example of defining driver for MySQL
<property name="driverClassName" value="com.mysql.jdbc.Driver />
<property name="url" value="jdbc:mysql://localhost:3306/[database_name]" />
//Credentials for DB acces
<property name="username" value="root" />
<property name="password" value="password" />
</bean>
<bean id="ExampleDAO" class="[your_file_path].JDBCExampleDAO">
<property name="dataSource" ref="dataSource" />
</bean>
... In Java Configuration(preferred):
...
@Bean
public DataSource dataSourceInit() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/[database_name]");
dataSource.setUsername("root");
dataSource.setPassword("password");
return dataSource;
}
@Bean
public JDBCExampleDAO accountDAOinit() {
JDBCExampleDAO myClass = new JDBCExampleDAO();
myClass.setDataSource(dataSourceInit());
return myClass;
}
... Now, let's try to retrieve something from the database. Let's suppose you have "ID" and "name" columns in your table.
If we need to create an object from the data we've got, it is common way to create the row-mapper class:
public class MyRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
MyClass myClass = new MyClass();
myClass.setId(rs.getInt("ID"));
myClass.setImgFilePath(rs.getString("name"));
return myClass;
}
} Now we go back to DAO class and try to retrieve one object:
@Override
// declare this worning type if you work with generic in Java
@SuppressWarnings({"unchecked"})
public MyClass viewImg(int id) {
String sql = "SELECT * FROM table_name WHERE ID = ?";
MyClass myclass = null;
try {
myclass = (MyClass) getJdbcTemplate().queryForObject(sql, new Object[]{id}, new MyRowMapper());
} catch (IncorrectResultSizeDataAccessException e) {
myclass = null;
}
return myclass;
}
If we have more complicated table with some foreign keys, for example, we can retrieve couple of objects depends of the selection data:
@Override
@SuppressWarnings("rawtypes")
public Set<MyClass> getObjects(int notUniqueForeignKey) {
String sql = "SELECT * FROM table_name WHERE foreign_key = ?";
Set<MyClass> myObjects = new HashSet<MyClass>();
List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql, new Object[] notUniqueForeignKey});
for (Map row : rows) {
MyClass myObject = new MyClass();
myObject.setId(Integer.parseInt(String.valueOf(row.get("ID"))));
myObject.setImgFilePath((String) row.get("name"));
myObject.setForeignKey((String) row.get("foreignKey"));
myObjects.add(myObject);
}
return images;
}
Let's get some separate value from the table:
@Override
public String searchForSomeValue(String id) {
String sql = "SELECT some_value FROM account WHERE ID = ?";
String some_value = getJdbcTemplate().queryForObject(
sql, new Object[]{id}, String.class);
return some_value; } !!! And the one important thing about all this !!!
I've spend a lot of time trying to find working solution for inserting data to DB and retrieving after that autogenerated value (primary key - ID for example). I've found this unreal with jdbcTemplate.
So, if you want to return some value from a method which inserts data to DB you must do this using KeyHolder and old-school mechanism:
@Override
public int putData(final String name) {
final String sql = "INSERT INTO table_name " +
"(name) VALUES (?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
getJdbcTemplate().update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection conection) throws SQLException{
PreparedStatement ps =
connection.prepareStatement(sql, new String[]{"ID"});
ps.setString(1, name);
return ps;
}
},
keyHolder
);
return keyHolder.getKey().intValue();
} We've consider most important things about main DB requests with JDBC.
If you've find this article interesting - subscribe and share to your friends.
Thanks for reading and goodbye!
Thanks for some imges: http://freedesignfile.com
License: Creative Commons (Attribution 3.0)
No comments:
Post a Comment