Friday, January 17, 2014

Spring Course: V36 - V49 Working with Databases

V36 Creating Database with MySQL
this is just a video about installing MySQL workbench and creating a table

V37 Using Property File
in this video we create jdbc.property file and add the connection information in it.

to add a property file and read it from spring you can do the following

1- create a package like com.project.prop
2- create a file JDBC.properties in the package.
3- add the connection information in JDBC.properties
jdbc.username=hnj
jdbc.pass=judge
4- now in beans.xml you should add the context schema
<beans xmlns:context="http://www.springframework.org/schema/context">
5- add also property-placeholder
<context:property-placeholder location="com.project.prop" />
by that spring knows from where to read the properties
6- now you can read the proerty file infomation by using ${jdbc.username}
<bean ...>
     <property name="userName" value="${jdbc.username}"/>
</bean>
7- or you can
@Autowired
public void setUserName(@Value("${jdbc.username}") String userName) {}

V38 Implementing the DAO Pattern
in this video we are doing some preparation,
1- crate OFFER db table with the fields email,ID,text
2- create Offer class


3- now in order to create DAO patter we define a class OfferDAO and we will add inside it the methods that deals with OFFER table:


V39 Connector Jar
simply, you need a JAR file to connect to MySQL database, you can add the required JAR file in POM.xml 
also add 
jdbc.driver=com.mysql.jdbc.Driver 
to jdbc.properties

we will use this value later.

V40 Configuring Connection Pooling With Apache DBCP
to connect to database we will use an Apache library for that, DBCP library has functionality to open, close, connection pool and so on.

to do that
1- add the required JAR in pom.xml
2- prepare all the required information in jdbc.properties


3- the file that we are gonna use from Apache DBCP is BasicDataSource, so we will create a bean of it in beans.xml


as you can see we set the destroy-method="close"
now we are ready to connect to the database.

V41 JDBC Templates
The most used class for JDCB is JdbcTemplate we will see here how we can use this class.

1- we will use JdbcTemplate in the OfferDAO that we defined before.
2- JdbcTemplate class take an input a DataSource
3- in our case the datasource will be the Apache BasicDataSource that we defined before

here is the OfferDAO

as you can see we made the class a bean by defining it as @Component.
the DataSource is Injected and used to build the JdbcTemplate, as you can see we didnt inject JdbcTemplate, we injected DataSource

now we can use the JdbcTemplate to create queries.

V42 Querying the Database 
one of the important method in JdbcTemplate is query which is used to run an sql query.
jdbcTemplate.query("sql statment", RowMapper)
the first parameter is the sql query, the second parameter is used to map the ResultSet returned to an Object, usually we use an anonymous class to do that

you can see how we define an anonymous class to implement the mapRow function from RowMapper

V43 Database Exception
Spring wrapped the database exceptions and gave us more detailed exceptions, the top class is DataAccessException alot of detailed classes inheret from this class.
Spring database exceptions are alos unchecked exceptions, so you are not forced to catch them.

V44 Named Parameters
in order to define a named query you should use NamedParameterJdbcTemplate,  you set the parameters value using MapSqlParameterSource



as you can see the named parameter is defined as :id in the sql statement.

V45 Update Statement
to run an update, insert or delete statement you should use the function update() not query()

the update() function return the number of affected rows.

V46 Getting Placeholder Values From Beans

in this video we run an insert statment, as we mentioned before you can use update() in order to insert a record in the database, however here in this example rather than using a MapSqlParameterSource we used BeanPropertySqlParameterSource

what BeanPropertySqlParameterSource does is mapping the named parameter to the properties of the provided object (in this case offer).
so :name will be mapped to the name property in the offer object, :text to text property and so on

V47 Adding Update to the DAO
Nothing new here he just added an update function to OfferDAO

V48 Batch Updates
you can run a batch update on a list of objects like this 


as you can see we use batchUpdate() function.
the list of named parameters should be of type SqlParameterSource.

the return value of batchUpdate() is int[] which reflects the number of affected row for each insert statement

V49 Transaction
in order to do transactions:
1- you should add a transaction manager which is a bean of type org.springframework.jdbc.datasource.DataSourceTransactionManager, this bean need a datasource so we will pass the Apache DBCP datasource
2- you need to add <tx:annotation-driven/> tag


3- now you can add the @Transactional attribute to any method that you want to run in transaction

No comments:

Post a Comment