log4j 格式化mysql sql语句_log4j+JDBC+mySQL 将对象写入数据库

一、写前唠叨

最近写了一个关于将对象通过log4j的形式保存到数据库的例子，因对这个log4j正在入门，所以做之前查过很多资料，不过网上的信息比较简单，如下：

1.所有信息几乎全部在配置文件中进行获取，如：数据库相关的连接、用户名、密码、sql语句；日志相关的appender、输出、格式化、参数；

2.写到数据库中的仅是一个字符串而不是一个对象，如：将一个对象本身以及它的各个数据作为一条记录存到数据库

3.代码的灵活性较低，如：只能存比较简单的数据类型，如保存一个对象及属性的话，就不太容易做了。

当然，网上的这些资料当然是为了最基础的入门而做的，因此简单了些，但也不乏比较深奥的资料，比如穿件连接池，考虑缓存等等。。

二、我的做法

1.通过继承JDBCAppender实现日志的写入的

2.通过JDBC连接数据库(也可通过HIBERNATE)

3.数据库和log4j的基本信息配置到properties中，但数据库可以自由选择多种数据库(在配置文件中配置即可)

4.将对象的属性存到库中

三、项目例子

1.项目结构图：见附件，项目结构图.png

2. 数据库：MYSQL  库名：LOG4J  表名：LogMessage/StudentMessage

表结构请看附件：LogMessage.png/StudentMessage.png

3.代码及说明

log4j.properties

:分别输出指定为：控制台、文件(log.log)、数据库

log4j.rootLogger=INFO,appender1,appender2,DATABASE

# console

log4j.appender.appender1=org.apache.log4j.ConsoleAppender

log4j.appender.appender1.layout=org.apache.log4j.PatternLayout

log4j.appender.appender1.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss:SSS}[%p]: %m%n

# file

log4j.appender.appender2=org.apache.log4j.FileAppender

log4j.appender.appender2.File=log.log

log4j.appender.appender2.layout=org.apache.log4j.PatternLayout

log4j.appender.appender2.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss:SSS}[%p]: %m%n

#mysql dbappender test

log4j.appender.DATABASE.driver=com.mysql.jdbc.Driver

log4j.appender.DATABASE.URL=jdbc:mysql://127.0.0.1:3306/LOG4J

log4j.appender.DATABASE.username=root

log4j.appender.DATABASE.password=root

log4j.appender.DATABASE=control.DbAppender

log4j.appender.DATABASE.layout=org.apache.log4j.PatternLayout

log4j.appender.DATABASE.layout.ConversionPattern=[framework] %d - %c -%-4r [%t] %-5p %c %x - %m%n %L

Conorl包——DbAppender.java

:日志提交中转站，提取日志信息，处理后进行数据库操作插入对应的表中

package control;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Hashtable;

import java.util.Iterator;

import java.util.Map;

import org.apache.log4j.jdbc.JDBCAppender;

import org.apache.log4j.spi.ErrorCode;

import org.apache.log4j.spi.LoggingEvent;

import bean.HashMapping;

import bean.Students;

import bean.UserBean;

import dao.JdbcUtils;

import dao.StudentsUtil;

import dao.UserUtil;

public class DbAppender extends JDBCAppender {

protected void closeConnection(Connection con) {

// TODO Auto-generated method stub

// super.closeConnection(con);

JdbcUtils.close(con);

}

@Override

protected void execute(String sql) throws SQLException {

// TODO Auto-generated method stub

// super.execute(arg0);

int flag = 0;

int end = sql.lastIndexOf(")");

String sqls = sql.substring(0, end + 1);

String beanname = sql.substring(end + 1);

if (beanname.equals("UserBean")) {

flag = UserUtil.insertUser(sqls);

} else if (beanname.equals("Students")) {

flag = StudentsUtil.insertStudent(sqls);

}

System.out.println(flag == 0 ? "insert fail!" : "insert successful!");

}

@Override

protected Connection getConnection() throws SQLException {

// TODO Auto-generated method stub

// return super.getConnection();

if (connection == null) {

connection = JdbcUtils.getConnection();

}

return connection;

}

@Override

protected String getLogStatement(LoggingEvent event) {

// TODO Auto-generated method stub

// return super.getLogStatement(event);

StringBuffer sbuf = new StringBuffer();

Hashtable hm = (Hashtable) event.getMessage();

String sql = null;

if ((hm.get("beanname")).equals("UserBean")) {

sbuf.append(UserUtil.insertSql());

sql = sbuf.toString();

int end = sql.lastIndexOf("(");

sql = sql.substring(0, end) + "('" + hm.get(HashMapping.USER_TIME)

+ "','" + event.getMessage() + "','"

+ hm.get(HashMapping.USER_USERNAME) + "','"

+ hm.get(HashMapping.USER_PASSWORD) + "')";

System.out.println(sql);

return sql + hm.get("beanname");

} else if ((hm.get("beanname")).equals("Students")) {

sbuf.append(StudentsUtil.insertSql());

sql = sbuf.toString();

int end = sql.lastIndexOf("(");

sql = sql.substring(0, end)

+ "('"

+ hm.get(HashMapping.STUDENT_NAME)

+ "','"

+ hm.get(HashMapping.STUDENT_AGE)

+ "','"

+ (((Integer) (hm.get(HashMapping.STUDENT_SEX))) == 1 ? "F"

: "M") + "','" + hm.get(HashMapping.STUDENT_TIME)

+ "')";

System.out.println(sql);

return sql + hm.get("beanname");

}

return sql;

}

}

Bean包中的类——HashMapping.java

：将属性设置成常量(即键-值对中的键)，便于代码中进行键值对的读取。

package bean;

public class HashMapping {

public static final String USER_USERNAME = "username";

public static final String USER_PASSWORD = "password";

public static final String USER_TIME = "time";

public static final String STUDENT_NAME = "name";

public static final String STUDENT_AGE = "age";

public static final String STUDENT_SEX = "sex";

public static final String STUDENT_TIME = "time";

}

Bean包中的类——UserBean.java/Students.java:

对LogMessage/studentMessage表及此对象持久化

package bean;

public class Students {

private String name;

private int age;

private int sex;

private String time;

public String getTime() {

return time;

}

public void setTime(String time) {

this.time = time;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

public int getSex() {

return sex;

}

public void setSex(int sex) {

this.sex = sex;

}

}

//===============================================

package bean;

import java.sql.Date;

public class UserBean {

private String username;

private String password;

private String Time;

private String Note;

public String getTime() {

return Time;

}

public void setTime(String time) {

Time = time;

}

public String getNote() {

return Note;

}

public void setNote(String note) {

Note = note;

}

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;

}

}

Dao中的包——GetObject.java:

将对象放到hashtable中并发送到dbappender中

package dao;

import java.lang.reflect.InvocationTargetException;

import java.lang.reflect.Method;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.Hashtable;

import org.apache.log4j.Logger;

import org.apache.log4j.PropertyConfigurator;

import test.Test;

import bean.Students;

import bean.UserBean;

public class GetObject {

private Logger logger = Logger.getLogger(GetObject.class);

private UserBean ub;

private Students st;

private SimpleDateFormat sf;

// 保存对象

public UserBean insertInfo(String username, String password) {

ub = new UserBean();

ub.setUsername(username);

ub.setPassword(password);

return ub;

}

public Logger getLogger() {

return logger;

}

public void setLogger(Logger logger) {

this.logger = logger;

}

public UserBean getUb() {

return ub;

}

public void setUb(UserBean ub) {

this.ub = ub;

}

public Students getSt() {

return st;

}

public void setSt(Students st) {

this.st = st;

}

public SimpleDateFormat getSf() {

return sf;

}

public void setSf(SimpleDateFormat sf) {

this.sf = sf;

}

public Students insertInfo(String name, int age, int sex) {

st = new Students();

st.setName(name);

st.setAge(age);

st.setSex(sex);

return st;

}

// bean key-value

public void getMethod(Object obj) {

sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");

PropertyConfigurator.configure("log4j.properties");

Hashtable ht = new Hashtable();

ht.clear();

if (obj instanceof UserBean) {

ht.put("username", ub.getUsername());

ht.put("password", ub.getPassword());

ht.put("time", sf.format(new Date()));

ht.put("beanname", "UserBean");

} else if (obj instanceof Students) {

ht.put("name", st.getName());

ht.put("age", st.getAge());

ht.put("sex", st.getSex());

ht.put("time", sf.format(new Date()));

ht.put("beanname","Students");

}

logger.info(ht);

}

}

Dao中的包——JdbcUtils.java:

数据库的动态链接

package dao;

import java.io.FileInputStream;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Iterator;

import java.util.Properties;

import java.util.Set;

public final class JdbcUtils {

//

private static String url = null;

private static String driver = null;

private static String username = null;

private static String password = null;

private JdbcUtils() {

}

static{

try {

getProperties();

Class.forName(driver);

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static Connection getConnection() throws SQLException {

return DriverManager.getConnection(url, username, password);

}

public static void close(Connection conn) {

try {

if (conn != null)

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void getProperties() {

Properties props = new Properties();

FileInputStream istream = null;

try {

istream = new FileInputStream("log4j.properties");

props.load(istream);

Set s = props.keySet();

Iterator it = s.iterator();

while (it.hasNext()) {

String id = (String) it.next();

String value = props.getProperty(id);

if(id.equals("log4j.appender.DATABASE.driver")){

driver = value;

}else if(id.equals("log4j.appender.DATABASE.URL")){

url = value;

}else if(id.equals("log4j.appender.DATABASE.username")){

username = value;

}else if(id.equals("log4j.appender.DATABASE.password")){

password = value;

}

//System.out.println(id + ":=" + value);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

try {

istream.close();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

Dao中的包——StudentsUtil.java/UserUtil.java:

对各自的表进行数据库的操作

package dao;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;

public class StudentsUtil {

public static int insertStudent(String sql) {

Connection conn = null;

Statement sm = null;

int flag = 0;

try {

conn = JdbcUtils.getConnection();

sm = conn.createStatement();

sm.execute(sql);

flag = 1;

} catch (SQLException e) {

// TODO Auto-generated catch block

System.out.println("insert fail!");

} finally {

if (sm != null) {

try {

sm.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

JdbcUtils.close(conn);

}

return flag;

}

public static String insertSql() {

return "INSERT INTO StudentMessage(Name,Age,Sex,Time) values(";

}

}

//===============================================

package dao;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import bean.UserBean;

public class UserUtil {

public static int insertUser(String sql) {

Connection conn = null;

Statement sm = null;

int flag = 0;

try {

conn = JdbcUtils.getConnection();

sm = conn.createStatement();

sm.execute(sql);

flag = 1;

} catch (SQLException e) {

// TODO Auto-generated catch block

System.out.println("insert fail!");

} finally {

if (sm != null) {

try {

sm.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

JdbcUtils.close(conn);

}

return flag;

}

public static String insertSql(){

return "INSERT INTO LogMessage(Time,Note,Username,Password) values(";

}

}

test包——test.java

:测试类

package test;

import java.lang.reflect.InvocationTargetException;

import org.apache.log4j.Logger;

import org.apache.log4j.MDC;

import org.apache.log4j.PropertyConfigurator;

import org.apache.log4j.jdbc.JDBCAppender;

import org.omg.CORBA.Request;

import dao.GetObject;

import bean.Students;

import bean.UserBean;

public class Test {

public static void main(String[] args) {

Test dbt = new Test();

dbt.testUser();

}

public void testUser() {

try {

GetObject go = new GetObject();

UserBean ub = go.insertInfo("EE-YY", "YY-12345");

go.getMethod(ub);

// Students st = go.insertInfo("YY", 21, 2);

// go.getMethod(st);

} catch (SecurityException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

结果见附加：stuM.png/logM.png

大小: 8.4 KB

大小: 9.7 KB

大小: 11.2 KB

大小: 661 Bytes

大小: 661 Bytes

1

顶

0

踩

分享到：

2012-03-20 18:28

浏览 3746

评论