【本文已迁移到“程序员文摘” http://programmerdigest.cn/category/lajp】常见的B/S的程序结构都有和数据库的交互,本示例中通过一个简单的增删改查的列子,介绍LAJP的通常使用方式。
一、创建库表结构,使用Mysql:
1. 先创建用户:
下面两条语句创建用户'ali',其登录密码'ali',可以从本地和网络登录,拥有数据库所有权限:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ali'@'localhost' IDENTIFIED BY 'ali' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ali'@'%' IDENTIFIED BY 'ali' WITH GRANT OPTION;
2. 使用用户ali登录Mysql:
$ mysql -u ali -pali
3. 创建数据库demo_users, 使用utf8字符集
mysql> create database demo_users CHARACTER SET utf8;
4. 创建表
mysql> create table demo(
id MEDIUMINT NOT NULL AUTO_INCREMENT,
login varchar(20) NOT NULL,
passwd varchar(20) NOT NULL,
userName varchar(10) NOT NULL,
age SMALLINT NOT NULL,
sex TINYINT NOT NULL,
PRIMARY KEY(id));
demo中字段含义如下:
id: 主键,自增序列
login: 用户帐号
passwd: 用户密码
userName: 用户名称
age: 用户年龄
sex: 用户性别,0女性 1男性
二、Java端
在LAJP架构中,Java负责和数据库交互,这里使用开源的组件搭建Java和数据库的连接:
- 数据库JDBC驱动:Mysql官方提供的mysql-connector-java
- 数据库连接池:使用开源的c3p0
- 日志组件:最常用的log4j
1. 首先,编写获得数据库交互的工具类
001 package com.googlecode.lajp.mysqldemo;
002 import java.beans.PropertyVetoException;
003 import java.sql.Connection;
004 import java.sql.SQLException;
005 import org.apache.log4j.Logger;
006 import com.mchange.v2.c3p0.ComboPooledDataSource;
007 public class DBUtil
008 {
009 static Logger log = Logger.getLogger(DBUtil.class);
010
011 private static ComboPooledDataSource cpds;
012
013 public static final void init()
014 {
015 cpds = new ComboPooledDataSource();
016 try
017 {
018 cpds.setDriverClass("om.mysql.jdbc.Driver");
019 }
020 catch (PropertyVetoException e)
021 {
022 log.error("加载数据库驱动异常", e);
023 throw new RuntimeException("Can't load Driver: om.mysql.jdbc.Driver" );
024 }
025 cpds.setJdbcUrl("jdbc:mysql://127.0.0.1/demo_users?characterEncoding=utf-8");
026 cpds.setUser("ali");
027 cpds.setPassword("ali");
028 }
029
030 public static Connection getConn() throws SQLException
031 {
032 return cpds.getConnection();
033 }
034 }
在类DBUtil中,编写了两个方法,init()用来初始化c3p0数据库连接池,018行加载JDBC驱动,025~027行声明连接参数,"jdbc:mysql://127.0.0.1/demo_users?characterEncoding=utf-8"表示连接到本机的demo_users库,字符集为utf-8。getConn()方法是公用的获取数据库连接的方法。
2. 对于Java面向对象语言,习惯以对象方式映射数据库表,这里编写一个和表demo映射的JavaBean类:
001 package com.googlecode.lajp.mysqldemo;
002
003 public class User
004 {
005 /** ID */
006 private int id;
007 /** 帐号 */
008 private String login;
009 /** 密码 */
010 private String passwd;
011 /** 用户名 */
012 private String userName;
013 /** 年龄 */
014 private int age;
015 /** 性别(true:女) */
016 private boolean girl;
017
0xx ......getter 和 setter 方法
0xx }
User中的每个属性和demo表的字段对应,并具有同业务含义相符合的数据类型。
3. 最后编写PHP调用的服务方法,代码比较长,这里只将第一个方法(增加方法)显示完整,其他方法只显示主要逻辑代码:
package com.googlecode.lajp.mysqldemo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;
/**
* Java服务
* @author diaoyf
*
*/
public class JavaService
{
static Logger log = Logger.getLogger(JavaService.class);
/**
* 增加用户服务
* @param newUser 用户对象
* @throws Exception
*/
public static final void addUser(User newUser) throws Exception
{
Connection conn = null;
PreparedStatement stmt = null;
try
{
conn = DBUtil.getConn();
StringBuffer sql = new StringBuffer();
sql.append("insert into demo");
sql.append("(");
sql.append(" login,");
sql.append(" passwd,");
sql.append(" userName,");
sql.append(" age,");
sql.append(" sex ");
sql.append(")values(");
sql.append(" ?,");
sql.append(" ?,");
sql.append(" ?,");
sql.append(" ?,");
sql.append(" ? ");
sql.append(")");
stmt = conn.prepareStatement(sql.toString());
stmt.setString(1, newUser.getLogin());
stmt.setString(2, newUser.getPasswd());
stmt.setString(3, newUser.getUserName());
stmt.setShort(4, (short)newUser.getAge());
stmt.setShort(5, (short)(newUser.isGirl() ? 0 : 1));
stmt.execute();
}
catch (Exception e)
{
log.error("增加新用户异常", e);
throw e;
}
finally
{
try
{
if (conn != null)
{
conn.close();
}
}
catch (SQLException e)
{
}
}
}
/**
* 删除用户服务
* @param id 用户ID
* @throws Exception
*/
public static final void delUser(int id)throws Exception
{
//....
StringBuffer sql = new StringBuffer();
sql.append("delete from demo where id = ?");
stmt = conn.prepareStatement(sql.toString());
stmt.setInt(1, id);
stmt.execute();
//....
}
/**
* 修改用户属性服务
* @param user
* @throws Exception
*/
public static final void modifyUser(User user)throws Exception
{
//....
StringBuffer sql = new StringBuffer();
sql.append("update demo set ");
sql.append(" login = ?,");
sql.append(" passwd = ?,");
sql.append(" userName = ?,");
sql.append(" age = ?,");
sql.append(" sex = ? ");
sql.append(" where id = ?");
stmt = conn.prepareStatement(sql.toString());
stmt.setString(1, user.getLogin());
stmt.setString(2, user.getPasswd());
stmt.setString(3, user.getUserName());
stmt.setShort(4, (short)user.getAge());
stmt.setShort(5, (short)(user.isGirl() ? 0 : 1));
stmt.setInt(6, user.getId());
stmt.executeUpdate();
//....
}
/**
* 根据ID查询用户服务
* @param id
* @return 用户对象
* @throws Exception
*/
public static final User getUser(int id)throws Exception
{
//....
StringBuffer sql = new StringBuffer();
sql.append("select * from demo where id = ?");
stmt = conn.prepareStatement(sql.toString());
stmt.setInt(1, id);
rs = stmt.executeQuery();
if (rs.next())
{
User user = new User();
user.setId(rs.getInt("id"));
user.setLogin(rs.getString("login"));
user.setPasswd(rs.getString("passwd"));
user.setUserName(rs.getString("userName"));
user.setAge(rs.getShort("age"));
user.setGirl((rs.getShort("sex") == 0) ? true : false);
return user;
}
else
{
throw new Exception("查询不到用户, id=" + id);
}
//....
}
/**
* 获取用户列表
* @return
*/
public static final ArrayList<User> userList()throws Exception
{
ArrayList<User> userList = new ArrayList<User>();
//....
String sql = "select * from demo";
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
while (rs.next())
{
User user = new User();
user.setId(rs.getInt("id"));
user.setLogin(rs.getString("login"));
user.setPasswd(rs.getString("passwd"));
user.setUserName(rs.getString("userName"));
user.setAge(rs.getShort("age"));
user.setGirl((rs.getShort("sex") == 0) ? true : false);
userList.add(user);
}
//....
return userList;
}
}
在JavaService类中,提供了增、删、改、查共5个方法,其中"查"有两个方法。
4. Java端运行
运行Java服务端,需要配置classpath,编写run.sh脚本:
run.sh
#!/bin/sh
# -----------------------------------------------------------
# LAJP-Java Service 启动脚本
#
# (2009-09 http://code.google.com/p/lajp/)
#
# -----------------------------------------------------------
# java服务中需要的jar文件或classpath路径,如业务程序、第三方jar文件log4j等
export other_classpath=/media/sda3/prog/eclipse_java_my/workspace/lajp_mysql_demo/lib/c3p0-0.9.1.2.jar:/media/sda3/prog/eclipse_java_my/workspace/lajp_mysql_demo/lib/mysql-connector-java-5.1.7-bin.jar:/media/sda3/prog/eclipse_java_my/workspace/lajp_mysql_demo/lib/log4j-1.2.8.jar:/media/sda3/prog/eclipse_java_my/workspace/lajp_mysql_demo/bin/
# 自启动类和方法,LAJP服务启动时会自动加载并执行
export AUTORUN_CLASS=com.googlecode.lajp.mysqldemo.DBUtil
export AUTORUN_METHOD=init
# 字符集设置 GBK|UTF-8
# export CHARSET=GBK
# LAJP服务启动指令(前台)
java -classpath .:lajp_9.09.jar:$other_classpath lajp.PhpJava
# LAJP服务启动指令(后台)
# nohup java -classpath lajp_9.09.jar:$other_classpath lajp.PhpJava &
设置other_classpath变量,引入c3p0-0.9.1.2.jar、 mysql-connector-java-5.1.7-bin.jar、 log4j-1.2.8.jar和Java端服务程序。
AUTORUN_CLASS和 AUTORUN_METHOD两个变量, 设置在启动时自动运行 DBUtil类中的init()方法,使Java端服务启动时加载Mysql的JDBC驱动,并配置数据库连接池。
三、PHP端
页面流程如下图:
1. index.html:
<html>
<head>
<meta http-equiv="refresh" content="0; url=list.php">
</head>
</html>
访问index.html会自动转向到程序的核心页面list.php,在list页面中显示用户列表。
2. list.php:
<?php header("Content-Type:text/html;charset=utf-8");
require_once("CLASS_user.php");
require_once("../../lajp/php_java.php");
try
{
//调用Java服务:获得用户列表集合
$list = lajp_call("com.googlecode.lajp.mysqldemo.JavaService::userList");
}
catch (Exception $e)
{
echo "获得用户列表出错:{$e}<br>";
return;
}
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>用户列表</title>
<script language="javascript">
function add()
{
document.form1.action = "toadd.php"
document.form1.submit();
}
function del()
{
document.form1.action = "del.php"
document.form1.submit();
}
function modify()
{
document.form1.action = "tomodify.php"
document.form1.submit();
}
</script>
</head>
<body>
<form name="form1" method="post" action="">
<table width="60%" border="1">
<caption>
用户列表
</caption>
<tr>
<td>帐号</td>
<td>密码</td>
<td>用户名</td>
<td>年龄</td>
<td>性别</td>
</tr>
<?php
//遍历用户列表集合
foreach ($list as $user)
{
?>
<tr>
<td><input type="radio" name="id" value="<?php echo $user->id; ?>"><?php echo $user->login; ?></td>
<td><?php echo $user->passwd; ?></td>
<td><?php echo $user->userName; ?></td>
<td><?php echo $user->age; ?></td>
<td><?php echo ($user->girl == TRUE ? "女" : "男"); ?></td>
</tr>
<?php
}
?>
</table>
<input type="button" value="新增用户" onClick="add();">
<input type="button" value="删除用户" onClick="del()">
<input type="button" value="修改用户" onClick="modify()">
</form>
<p> </p>
</body>
</html>
3. 增加用户流程主要两个由PHP文件组成
toadd.php:
<?php header("Content-Type:text/html;charset=utf-8"); ?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>增加新用户</title>
</head>
<body>
<form name="form1" method="post" action="doadd.php">
<table width="80%" border="1">
<caption>
增加用户
</caption>
<tr>
<th align="right" scope="row">帐号</th>
<td><input type="text" name="login"></td>
</tr>
<tr>
<th align="right" scope="row">密码</th>
<td><input type="text" name="passwd"></td>
</tr>
<tr>
<th align="right" scope="row">用户名</th>
<td><input type="text" name="userName"></td>
</tr>
<tr>
<th align="right" scope="row">年龄</th>
<td><input type="text" name="age"></td>
</tr>
<tr>
<th align="right" scope="row">性别</th>
<td><input name="girl" type="radio" value="false" checked>
男
<input type="radio" name="girl" value="true">
女</td>
</tr>
</table>
<p>
<input type="submit" name="Submit" value="提交">
</p>
</form>
</body>
</html>
doadd.php:
<?php header("Content-Type:text/html;charset=utf-8");
require_once("CLASS_user.php");
$user = new com_googlecode_lajp_mysqldemo_User;
//接收表单提交的数据
$user->login = $_POST["login"];
$user->passwd = $_POST["passwd"];
$user->userName = $_POST["userName"];
$user->age = (int)$_POST["age"];
$user->girl = $_POST["girl"] == "true" ? TRUE : FALSE;
//省略数据校验过程...
require_once("../../lajp/php_java.php");
try
{
//调用Java服务:增加用户
lajp_call("com.googlecode.lajp.mysqldemo.JavaService::addUser", $user);
//重定向
header("Location: list.php");
}
catch (Exception $e)
{
echo "增加用户出错:{$e}<br>";
}
?>
4. 删除
del.php
<?php header("Content-Type:text/html;charset=utf-8");
//要删除的用户ID
$id = (int)$_POST["id"];
require_once("../../lajp/php_java.php");
try
{
//调用Java服务:删除用户
lajp_call("com.googlecode.lajp.mysqldemo.JavaService::delUser", $id);
//重定向
header("Location: list.php");
}
catch (Exception $e)
{
echo "删除用户出错:{$e}<br>";
}
?>
5. 修改用户流程也由两个PHP文件构成:
tomodify.php
<?php header("Content-Type:text/html;charset=utf-8"); ?>
<?php
require_once("CLASS_user.php");
//要修改的用户ID
$id = (int)$_POST["id"];
require_once("../../lajp/php_java.php");
try
{
//调用Java服务:查询用户
$user = lajp_call("com.googlecode.lajp.mysqldemo.JavaService::getUser", $id);
}
catch (Exception $e)
{
echo "查询用户出错:{$e}<br>";
}
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>修改用户</title>
</head>
<body>
<form name="form1" method="post" action="domodify.php">
<table width="80%" border="1">
<input type="hidden" name="id" value="<?php echo $user->id; ?>">
<caption>
修改用户
</caption>
<tr>
<th align="right" scope="row">帐号</th>
<td><input type="text" name="login" value="<?php echo $user->login; ?>"></td>
</tr>
<tr>
<th align="right" scope="row">密码</th>
<td><input type="text" name="passwd" value="<?php echo $user->passwd; ?>"></td>
</tr>
<tr>
<th align="right" scope="row">用户名</th>
<td><input type="text" name="userName" value="<?php echo $user->userName; ?>"></td>
</tr>
<tr>
<th align="right" scope="row">年龄</th>
<td><input type="text" name="age" value="<?php echo $user->age; ?>"></td>
</tr>
<tr>
<th align="right" scope="row">性别</th>
<?php
if ($user->girl)
{
?>
<td>
<input name="girl" type="radio" value="false">男
<input type="radio" name="girl" value="true" checked>女
</td>
<?php
}
else
{
?>
<td>
<input name="girl" type="radio" value="false" checked>男
<input type="radio" name="girl" value="true">女
</td>
<?php
}
?>
</tr>
</table>
<p>
<input type="submit" name="Submit" value="提交">
</p>
</form>
</body>
</html>
domodify.php
<?php header("Content-Type:text/html;charset=utf-8");
require_once("CLASS_user.php");
$user = new com_googlecode_lajp_mysqldemo_User;
//接收表单提交的数据
$user->id = (int)$_POST["id"];
$user->login = $_POST["login"];
$user->passwd = $_POST["passwd"];
$user->userName = $_POST["userName"];
$user->age = (int)$_POST["age"];
$user->girl = $_POST["girl"] == "true" ? TRUE : FALSE;
//省略数据校验过程...
require_once("../../lajp/php_java.php");
try
{
//调用Java服务:修改用户
lajp_call("com.googlecode.lajp.mysqldemo.JavaService::modifyUser", $user);
//重定向
header("Location: list.php");
}
catch (Exception $e)
{
echo "修改用户出错:{$e}<br>";
}
?>
在上面的php文件中通过require_once函数导入了两个文件,其中"php_java.php"是LAJP框架提供的,"CLASS_user.php"是和Java交互时的传递对象,代码如下:
CLASS_user.php:
<?php
//用户类
class com_googlecode_lajp_mysqldemo_User
{
var $id;
var $login;
var $passwd;
var $userName;
var $age;
var $girl;
}
?>