Posts Tagged ‘mysql

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!!!

28
Feb
10

How to Grep Multiple Lines

This page describes how to find and replace patterns that span across multiple lines.

Once I was coding up some SQL DDL’s and came across a problem. The program I used to generate the DDL did not generate the foreign key relationships such that they could be read by mySQL. The create table looked like this…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i)
                             REFERENCES pwd_master, 
       FOREIGN KEY (category_i)
                             REFERENCES pwd_category_master
);

The problem seen above is that mySQL does not like the above format. Instead it wants the DDL to look like this…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i) REFERENCES pwd_master  (passwd_i),
       FOREIGN KEY (category_i) REFERENCES pwd_category_master (category_i)
);


Based on sed(1)’s man page you can execute a series of commands within the { } block.

One of the commands is N;
n N Read/append the next line of input into the pattern space.

Step 1

Detect the pattern “FOREIGN KEY” and read the next line.

/FOREIGN KEY/ – search for lines starting with FOREIGN KEY

sed -e '/FOREIGN KEY/{N;p}'

Step 2

{} – apply the enclosed when the preceeding match is made
N – append the next line to the pattern buffer
p – prints the buffer that was just read

The resulting conversion looks like this…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i)
                             REFERENCES pwd_master, 
       FOREIGN KEY (passwd_i)
                             REFERENCES pwd_master, 
       FOREIGN KEY (category_i)
                             REFERENCES pwd_category_master
       FOREIGN KEY (category_i)
                             REFERENCES pwd_category_master
);

As you can see that the lines that matched are duplicated…

Step 4

Next we apply a substution using the following pattern

s/FOREIGN KEY \((.*)\).*REFERENCES \(.*\)/FOREIGN KEY \1 REFERENCES \2 \1,/

sed -e '/FOREIGN KEY/{N;s/FOREIGN KEY \((.*)\).*REFERENCES \(.*\)/FOREIGN KEY \1 REFERENCES \2 \1,/p}'

This results in output like this…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i) REFERENCES pwd_master,  (passwd_i),
       FOREIGN KEY (passwd_i) REFERENCES pwd_master,  (passwd_i),
       FOREIGN KEY (category_i) REFERENCES pwd_category_master (category_i),
       FOREIGN KEY (category_i) REFERENCES pwd_category_master (category_i),
);

Step 5

Next we get rid of the dups by appending a:
s/\n.*// – delete everything following the first \n.

sed -e '/FOREIGN KEY/{N;s/FOREIGN KEY \((.*)\).*REFERENCES \(.*\)/FOREIGN KEY \1 REFERENCES \2 \1,/;s/\n.*//p}'

This results in output like this…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i) REFERENCES pwd_master,  (passwd_i),
       FOREIGN KEY (category_i) REFERENCES pwd_category_master (category_i),
);

Step 6

Next we need to apply some additional clean-up patterns.

sed -e 's/,.*(/ (/' |
sed -e 's/^);/) ENGINE=InnoDB;/'

This results in…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i) REFERENCES pwd_master (passwd_i),
       FOREIGN KEY (category_i) REFERENCES pwd_category_master (category_i),
) ENGINE=InnoDB;

The only thing remaining is to cleanup the extra comma. Ohh well. I will fix this some other time…




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

Join 74 other followers

March 2017
S M T W T F S
« Mar    
 1234
567891011
12131415161718
19202122232425
262728293031  

Blog Stats

  • 800,977 hits