example with spring jdbctemplate with transactions.

If you want to use aop of spring and do a transaction with database you can use @Transaction annotation.  this is how you do it.

Note that this works only with non MyISAM tables. MyISAM does not handle transactions.

Say there is a function you want to have the transaction. then you simply add the @Transaction annotation above the method.
public class BeverageModel{
private DriverManagerDataSource dataSource;
private JdbcTemplate jdbcTemplate;

public void setDataSource(DriverManagerDataSource dataSource){
this.dataSource = dataSource;
}

public DriverManagerDataSource getDataSource() {
return dataSource;
}

public JdbcTemplate getJdbcTemplate() {

return jdbcTemplate;

}

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}

Spring 3 security login example with database help

dependencies
spring 3.x  standard library
spring security web
spring security core
spring security config

First create a mysql database called loginexample. The sql code is as following.

CREATE TABLE `users` (`USER_ID` INT(10) UNSIGNED NOT NULL,`USERNAME` VARCHAR(45) NOT NULL,`PASSWORD` VARCHAR(45) NOT NULL,`ENABLED` tinyint(1) NOT NULL,PRIMARY KEY (`USER_ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_roles` (`USER_ROLE_ID` INT(10) UNSIGNED NOT NULL,`USER_ID` INT(10) UNSIGNED NOT NULL,`AUTHORITY` VARCHAR(45) NOT NULL,PRIMARY KEY (`USER_ROLE_ID`),KEY `FK_user_roles` (`USER_ID`),CONSTRAINT `FK_user_roles` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO users (USER_ID, USERNAME,PASSWORD, ENABLED)VALUES (100, 'madhumal', '123456', TRUE);

INSERT INTO.user_roles (USER_ROLE_ID, USER_ID,AUTHORITY)VALUES (1, 100, 'ROLE_ADMIN')

INSERT INTO users (USER_ID, USERNAME,PASSWORD, ENABLED)VALUES (101, 'user', '123456', TRUE);

INSERT INTO user_roles (USER_ROLE_ID, USER_ID,AUTHORITY)VALUES (2, 101, 'ROLE_USER')

Note that we have two user roles, admin and user.

Create a Dynamic web project with the name SpringLogin

create your mvc-dispatcher-servlet.xml in WEB-INF folder

put the following code in there

spring jdbc template with mysql

Required Libraries are,

commons-logging
spring-jdbc
spring-beans
spring-core
spring-tx
spring-asm
mysql-connector-java
spring-context
spring-context-support
spring-expression

You can do this simply by using maven. Just google maven dependencies for spring jdbc template and put the dependencies in your pom.xml file and you are good to go.

Let's see how to use jdbc template in spring to communicate with mysql database.

Create a model class named Book.


package mad.library.form;

public class Book {

private String name;
private String author;
private String category;

public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}

}

Now create the BookDataManager class as following.

Jdbc driver time out.

Recently I deployed an app on glassfish and it worked really well for about half a day and then it threw an exception each time the service was called. Following is the excetion

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No 

operations allowed after statement closed.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance 

(NativeConstructorAccessorImpl. java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Deleg

Later found out that the jdbc driver connection automatically 

expires after 12 hours.  Setting conncetTimeout to 0 in the 

jdbc connect url as a parameter resolved the problem.