Spring JdbcDaoSupport Tutorial

Spring JdbcDaoSupport Tutorial

note: this is a Java config based spring mvc project. I don’t like namespace config.

1. what you need

1. a spring project.
2. jars you need to connect to database

2.Let’s begin our journey

configure dataSource bean

@Configuration
@ComponentScan(basePackages = {"com.helloalanturing.messageboard"})
public class MyRootConfig {
@Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/messageboard?verifyServerCertificate=false&useSSL=true");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        return dataSource;
    }
}

configure spring Application Context

public class MyWebAppInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {
    protected Class<?>[] getRootConfigClasses() {
        return new Class[]{MyRootConfig.class};
    }
    protected Class<?>[] getServletConfigClasses() {
        return new Class[]{MyWebConfig.class};//you may do not need this class to test JdbcDaoSupport, so just return null instead.
    }
    protected String[] getServletMappings() {
        return new String[] {"/"};
    }
}

Then we move to the real deal.

assume that you already created a database named “whatever_your_db_name_is”, and created a table “user”,like:

CREATE TABLE `user` (
  `userid` VARCHAR(16) COLLATE latin1_swedish_ci NOT NULL,
  `username` VARCHAR(32) COLLATE latin1_swedish_ci NOT NULL,
  `password` VARCHAR(16) COLLATE latin1_swedish_ci NOT NULL,
  `gender` VARCHAR(1) COLLATE latin1_swedish_ci DEFAULT NULL,
  `birthday` DATE DEFAULT NULL,
  `email` VARCHAR(32) COLLATE latin1_swedish_ci NOT NULL,
  PRIMARY KEY USING BTREE (`userid`)
) ENGINE=InnoDB
ROW_FORMAT=DYNAMIC CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'
;

User Entity

public class User extends Entity {
    private String userid;
    private String username;
    private String password;
    private String gender;
    private Date birthday;
    private String email;

  //getters and setters...........
}

UserDAO Interface

public interface UserDAO {
    List<User> listAllUsers();
}

UserDAOImpl implementation

@Repository("userDAO")
public class UserDAOImpl extends JdbcDaoSupport implements UserDAO {
    @Resource
    private DataSource dataSource;
    @PostConstruct
    private void initialize(){
        setDataSource(dataSource);
    }

    public List<User> listAllUsers() {
        String sql = "select * from user";
        List<User> list = getJdbcTemplate().queryForList(sql, User.class);
        return list;
    }
}

All done

all you need to do now is using whatever test tools you familiar with (say: junit) to test if it works.