This page will describe how to create a spring MVC application to test a connection to MySQL database. The user will provide the connection URL and username/password. The system will make a simple connection to the database and return the results to the screen.
Note: This page was originally written in November 15 2009.
Requirements
- Access to a MySQL Server
- Maven 2
- Eclipse
- Spring 2.5
- Java 1.5 or above
Start a new Project
Start a new project by creating it from an archetype.
mvn archetype:generate -DarchetypeArtifactId=maven-archetype-webapp
Choose option:
Answer the questions like this:
Define value for groupId: : test Define value for artifactId: : spring-mysql-test Define value for version: 1.0-SNAPSHOT: : Define value for package: test: : Confirm properties configuration: groupId: test artifactId: spring-mysql-test version: 1.0-SNAPSHOT package: test Y: :
cd spring-mysql-test
modify pom.xml and insert the following dependencies
<dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.9</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring</artifactId> <version>2.5.6</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>2.5.6</version> </dependency> <dependency> <groupId>org.apache.geronimo.specs</groupId> <artifactId>geronimo-servlet_2.5_spec</artifactId> <version>1.2</version> <type>jar</type> <scope>provided</scope> </dependency> </dependencies>
insert the following into
src/main/webapp/WEB-INF/web.xml
<!DOCTYPE web-app PUBLIC
"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.com/dtd/web-app_2_3.dtd" >
<web-app>
<display-name>Archetype Created Web Application</display-name>
<servlet>
<servlet-name>spring</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>spring</servlet-name>
<url-pattern>/app/*</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>/app/helloWorld</welcome-file>
</welcome-file-list>
</web-app>
Insert the following into
src/main/webapp/WEB-INF/spring-servlet.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
<context:component-scan base-package="test" />
<bean id="viewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/WEB-INF/jsp/" />
<property name="suffix" value=".jsp" />
</bean>
</beans>
Java Code
Before writing the Java files please create the following directory.
mkdir -p src/main/java/test
Create a java class that looks like this…
src/main/java/test/HelloWorldController.java
package test;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.AbstractController;
import org.springframework.stereotype.Controller;
@Controller("/helloWorld")
public class HelloWorldController extends AbstractController {
@Override
protected ModelAndView handleRequestInternal(HttpServletRequest request,
HttpServletResponse response) throws Exception {
ModelAndView mav = null;
// control logic goes here
mav = new ModelAndView("helloWorld");
return mav;
}
}
mkdir -p src/main/webapp/WEB-INF/jsp
src/main/webapp/WEB-INF/jsp/helloWorld.jsp
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en"> <head> <title>HelloWorld test page</title> <meta http-equiv="Content-type" content="text/html; charset=iso-8859-1" /> <meta http-equiv="Content-Language" content="en-us" /> </head> <body> Hello World page! </body> </html>
Run the Project
In order to run the project we need to put a couple of more things into pom.xml file.
1. The jetty plugin will allow Maven to run the project in the jetty servlet container. In the plug-in section of the pom.xml file put in the following…
2. By default Maven uses an old version of the JDK to compile. We need to set it to use a higher version. (supports annotations). If you don’t have 1.6 then change the source and target elements below to 1.5 at least.
pom.xml inside the build tag
<build>
<finalName>spring-mysql-test</finalName>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.0.2</version>
<configuration>
<source>1.6</source>
<target>1.6</target>
</configuration>
</plugin>
<plugin>
<groupId>org.mortbay.jetty</groupId>
<artifactId>jetty-maven-plugin</artifactId>
<version>7.0.0.v20091005</version>
<configuration>
<scanIntervalSeconds>1</scanIntervalSeconds>
</configuration>
</plugin>
</plugins>
</build>
To run the web application type:
type
mvn jetty:run
Navigate your browser to http://localhost:8080/app/helloWorld to test if everything is okay up to this point. If something is not working please review the steps seen above and make sure you at least see hello world printed on the screen.
We will create a Controller that draws a input form that the user can use to specify database url, username, password.
src/main/java/test/DBParam.java
package test;
public class DBParam {
public String url;
public String username;
public String password;
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
The controller will take this form submission and make a connection to the database with the results printed to the screen.
src/main/java/test/TestDBConnectionController.java
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.validation.BindException;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.SimpleFormController;
@Controller("/dbParameters")
public class TestDBConnectionController extends SimpleFormController {
public TestDBConnectionController() {
setFormView("dbParameters");
setCommandClass(DBParam.class);
setSuccessView("success");
setCommandName("dbParam");
}
@Override
protected ModelAndView processFormSubmission(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception {
System.out.println("form submitted");
DBParam dbParam = (DBParam)command;
String url = dbParam.getUrl();
String username = dbParam.getUsername();
String password = dbParam.getPassword();
// make a connection
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection(url, username, password);
System.out.println("got a connection: " + conn);
return super.processFormSubmission(request, response, command, errors);
}
}
We have enabled component scanning in our spring-servlet.xml file so we don’t have to register this controller in the xml file.
src/main/webapp/WEB-INF/jsp/dbParameters.jsp
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en"> <head> <title>DB Parameters</title> <meta http-equiv="Content-type" content="text/html; charset=iso-8859-1" /> <meta http-equiv="Content-Language" content="en-us" /> </head> <body> <form method="post"> <label>JDBC connect string: </label><input type="text" name="url" value="jdbc:mysql://hostname:3306/dbname" size="55"/><br/> <label>password: </label><input type="text" name="username"/><br/> <label>username: </label><input type="password" name="password"/><br/> <input type="submit" value="submit"/><br/> </form> </body> </html>
src/main/webapp/WEB-INF/jsp/success.jsp
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en"> <head> <title>DB Parameters</title> <meta http-equiv="Content-type" content="text/html; charset=iso-8859-1" /> <meta http-equiv="Content-Language" content="en-us" /> </head> <body> success </body> </html>
Run the project
You may run the project under jetty
mvn jetty:run
navigate your browser to:
http://localhost:8080/app/dbParameters
Replace the URL with the url to your mysql database.
