Posts Tagged ‘archetype

25
Feb
11

MySQL Test Connection using Spring MVC

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.

That’s it for now!!!

Advertisements



Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 77 other followers

November 2017
S M T W T F S
« Oct    
 1234
567891011
12131415161718
19202122232425
2627282930  

Blog Stats

  • 842,358 hits