MySQL JDBC Counter Add

Dieses Programm incrementiert für einen angegebenen URL den Zählerstand um 1.

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MysqlCountAdd {

    static final String mysqlDriver = "com.mysql.jdbc.Driver";

    static String dbConnect = "jdbc:mysql://localhost/inet";
    static String user = "user";
    static String pword = "password";

    public static void main(String[] args) {

	if ( args.length <= 0 ) {
	   System.out.println("usage: MysqlCountAdd <url>");
	   return;
	}
	String url = args[0];

        // load mysql driver
	try {
	    Class.forName( mysqlDriver );
	    System.out.println("mysqlDriver " + mysqlDriver + " loaded");
	} catch (ClassNotFoundException e) {
	    e.printStackTrace();
	}

        // connect to database
	Connection dbCon = null;
	try {
	    dbCon = DriverManager.getConnection(dbConnect,user,pword);
	    System.out.println("connected to " + dbConnect);
	    dbCon.setAutoCommit( false );
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	if ( dbCon == null ) {
	    return;
	}

        // work with database
	// increment counter of given url
	PreparedStatement stmt = null;
	PreparedStatement ustmt = null;
	ResultSet rs = null;
	String query = "SELECT count FROM counter WHERE url = ?;";
	String update = "UPDATE counter SET count = count + 1 WHERE url = ?";
	String insert = "INSERT INTO counter (url,count) VALUES (?,?)";
	try {
            stmt = dbCon.prepareStatement(query);
            stmt.setString(1, url );
	    System.out.println("statement prepared " + stmt);

	    rs = stmt.executeQuery();
	    int count = 1;
            int rows = 0;
	    if ( rs.next() ) {
		count = rs.getInt("count");
                System.out.println("url = " + url + ", count = " +count);
                ustmt = dbCon.prepareStatement(update);
		ustmt.setString(1, url );
		rows = ustmt.executeUpdate();
	    } else {
                ustmt = dbCon.prepareStatement(insert);
		ustmt.setString(1, url );
		ustmt.setInt(2, count );
		rows = ustmt.executeUpdate();
	    }
            System.out.println(""+ustmt + "\nrows = " + rows);
	    dbCon.commit();
	    rs.close();
	    if ( stmt != null ) {
               stmt.close();
	    }
	    if ( ustmt != null ) {
               ustmt.close();
	    }
	} catch (SQLException e) {
	    try { 
                dbCon.rollback();
	    } catch (SQLException e1) { 
            }
	    e.printStackTrace();
	}

	try {
            if ( dbCon != null ) {
               dbCon.close();
               System.out.println(dbConnect + " closed");
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	}
    }
}

Ausgabe mit UPDATE:

java MysqlCountAdd /srv/www/htdocs/hallo.html

mysqlDriver com.mysql.jdbc.Driver loaded
connected to jdbc:mysql://localhost/inet
statement prepared com.mysql.jdbc.PreparedStatement@a1807c: 
  SELECT count FROM counter WHERE url = '/srv/www/htdocs/hallo.html';
url = /srv/www/htdocs/hallo.html, count = 6
com.mysql.jdbc.PreparedStatement@fa7e74: 
  UPDATE counter SET count = count + 1 WHERE url = '/srv/www/htdocs/hallo.html'
rows = 1
jdbc:mysql://localhost/inet closed

Ausgabe mit INSERT:

java MysqlCountAdd /srv/www/htdocs/neu.html
mysqlDriver com.mysql.jdbc.Driver loaded
connected to jdbc:mysql://localhost/inet
statement prepared com.mysql.jdbc.PreparedStatement@a1807c: 
  SELECT count FROM counter WHERE url = '/srv/www/htdocs/neu.html';
com.mysql.jdbc.PreparedStatement@fa7e74: 
  INSERT INTO counter (url,count) VALUES ('/srv/www/htdocs/neu.html',1)
rows = 1
jdbc:mysql://localhost/inet closed

© Universität Mannheim, Rechenzentrum, 2004.

Last modified: Sat Jun 5 15:25:02 CEST 2004