LDSTechForumProjects

Database Development 1

This training covers JDBC, Sprig JDBC, connection pooling, and database configuration in the Java Stack.

Prerequisites

Slide Deck

Media:DBDevelopment.pdf

JDBC, Connection Pooling and Java Stack Config

Presentation

Lab 1

  • Download the db development project template
  • Unzip the artifact where you typically keep training or other projects
  • Open the db-training folder as a workspace in your LDS Tech IDE
  • Import the db1 project (and sub-projects)
    • File -> Import
    • Expand Maven
    • Choose "Existing Maven Projects"
    • point it to ...db-training/db1 as the root directory
    • click finish and wait for it to complete
    • NOTE: the solutions are available in a project named db1demo, repeat these steps if you want to import the solutions in to your IDE
    • ALSO: the jdbc-examples project is included, you can import that project if you want
  • Configure your project to use a connection pool
    • Open server.xml from db1-deploy/src/main/resources
      • add a GlobalNamingResource to configure your connection pool - look for "add GlobalNamingResourceHere"
<GlobalNamingResources>
		<Resource name="jdbc/default/DataSource"
				  auth="Container" 
		    	  type="javax.sql.DataSource" 
		    	  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
		    	  testWhileIdle="true"
		    	  testOnBorrow="true"
		    	  testOnReturn="false"
		    	  maxActive="${ds.maxPoolSize}" 
		    	  removeAbandonedTimeout="120"
		    	  removeAbandoned="true"
		    	  logAbandoned="true"
		    	  jmxEnabled="true"
		    	  jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;org.apache.tomcat.jdbc.pool.interceptor.StatementDecoratorInterceptor;org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx;org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"
		    	  username="${ds.username}" 
		    	  password="${ds.password}"
		    	  url="${ds.url}"
		    	  driverClassName="org.h2.Driver"
		    	  validationQuery="call 1" />
    </GlobalNamingResources>
    • Open db1-deploy/src/main/resources/local.catalina.properties
      • add connection and pool properties for the h2 in-memory database - look for "# DataSource Properties"
ds.url=jdbc:h2:mem:stack-starter;MODE=Oracle
ds.username=
ds.password=
ds.maxPoolSize=25
    • Open db1-web/src/main/webapp/META-INF/context.xml
      • add a ResourceLink to make the global JNDI resource available to the application - look for "add ResourceLink here"
<ResourceLink name="jdbc/default/DataSource" global="jdbc/default/DataSource"/>
    • Open db1-web/src/main/resources/META-INF/spring/applicationContext.xml
      • Configure the connction with a stack-db:data-source entry - look for "add stack-db:data-source here"
<stack-db:data-source jndi-name="jdbc/default/DataSource"/>
    • start your db1-1.0 (Tomcat...) server and navigate to http://localhost/db1
      • Your application should start without errors and the home page should display successfully however, the Manage Example part of the application WILL NOT WORK. In the next lab you will implement the methods required to make it work.
    • stop your server

Lab 1 Solution

JDBC (Continued), and Spring JDBC

Presentation

Lab 2

  • implement getAllExamples() in db1-web/src/main/java/org/lds/stack/training/service/ExampleServiceImpl.java
    • remove the UnsupportedOperationException
    • add the following code...
return jdbcTemplate.query("select * from EXAMPLE order by DATA", new ExampleRowMapper());
  • in the same file, finish implementing createExample() using the SimpleJdbcInsert that was created in the constructor
    • remove the UnsupportedOperationException
    • add the following code...
Map<String, Object> parameters = new HashMap<>();
parameters.put("EXAMPLE_NAME", example.getName());
parameters.put("DATA", example.getData());
exampleModelJdbcInsert.execute(parameters);
  • run the application and make sure you can list examples and create a new “example”

Lab 2 Solution

Best Practices / Conclusion

Presentation

This page was last modified on 20 June 2013, at 14:15.

Note: Content found in this wiki may not always reflect official Church information. See Terms of Use.