/**
* Project: ${zebra-client.aid}
*
* File Created at 2011-6-30
* $Id$
*
* Copyright 2010 dianping.com.
* All rights reserved.
*
* This software is the confidential and proprietary information of
* Dianping Company. ("Confidential Information"). You shall not
* disclose such Confidential Information and shall use it only in
* accordance with the terms of the license agreement you entered into
* with dianping.com.
*/
package com.dianping.zebra.shard.jdbc;
import junit.framework.Assert;
import org.junit.Test;
import com.dianping.zebra.shard.jdbc.base.SingleDBBaseTestCase;
import javax.sql.DataSource;
import java.sql.*;
import java.util.Date;
/**
*
* @author Leo Liang
*
*/
public class SingleDBPreparedStatementGroupFollowNoteIntegrationTest extends SingleDBBaseTestCase {
/*
* (non-Javadoc)
*
* @see com.dianping.zebra.jdbc.ZebraDBTestCase#getDBUrl()
*/
@Override
protected String getDBUrl() {
return "jdbc:h2:mem:zebra_ut;DB_CLOSE_DELAY=-1";
}
/*
* (non-Javadoc)
*
* @see com.dianping.zebra.jdbc.ZebraDBTestCase#getDataSetFilePath()
*/
@Override
protected String getDataSetFilePath() {
return "db-datafiles/data-singledb-group-follownote-integrationtest.xml";
}
/*
* (non-Javadoc)
*
* @see com.dianping.zebra.jdbc.ZebraDBTestCase#getCreateTableScriptPath()
*/
@Override
protected String getCreateTableScriptPath() {
return "db-datafiles/createtable-singledb-group-follownote-integrationtest.xml";
}
/*
* (non-Javadoc)
*
* @see com.dianping.zebra.jdbc.ZebraDBTestCase#getSpringConfigLocations()
*/
@Override
protected String[] getSpringConfigLocations() {
return new String[] { "ctx-singledb-group-follownote-integrationtest.xml" };
}
@Test
public void test1() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT FN.FollowNoteID AS NoteID, N.GroupID, FN.UserID, CONCAT('RE:', N.NoteTitle) AS NoteTitle, '' AS NoteBody, FN.AddTime, FN.LastIP, G.GroupName, G.GroupPermaLink, FN.NoteID AS GroupNoteID FROM DP_GroupFollowNote FN JOIN DP_GroupNote N ON N.NoteID = FN.NoteID JOIN DP_Group G ON N.GroupID = G.GroupID WHERE FN.NoteClass = 3 ORDER BY FN.AddTime LIMIT 0, 10");
stmt.execute();
ResultSet rs = stmt.getResultSet();
int count = 0;
System.out.println("Test 1 ------------------------------------------------------");
while (rs.next()) {
count++;
System.out
.println(" NoteID: " + rs.getObject(1) + " GroupID: " + rs.getObject(2) + " UserID: "
+ rs.getObject(3) + " NoteTitle: " + rs.getString(4) + " NoteBody: " + rs.getString(5)
+ " AddTime: " + rs.getObject(6) + " LastIP: " + rs.getString(7) + " GroupName: "
+ rs.getString(8) + " GroupPermaLink: " + rs.getString(9) + " GroupNoteID: "
+ rs.getObject(10));
}
System.out.println("Test 1 ------------------------------------------------------");
Assert.assertEquals(8, count);
} catch (Exception e) {
e.printStackTrace();
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test2() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("UPDATE DP_GroupFollowNote SET NoteClass = ? WHERE UserID = ?");
stmt.setInt(1, 4);
stmt.setInt(2, 1);
stmt.execute();
Statement stmt2 = conn.createStatement();
stmt2.execute("select count(*) total from DP_GroupFollowNote where NoteClass=4");
ResultSet rs = stmt2.getResultSet();
while (rs.next()) {
Assert.assertEquals(3, rs.getLong(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test3() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT N.GroupID, F.FollowNoteID, F.UserID, F.NoteId FROM DP_GroupFollowNote F INNER JOIN DP_GroupNote N ON N.NoteID = F.NoteID WHERE F.UserID = ? AND F.NoteClass <> 3");
stmt.setInt(1, 0);
stmt.execute();
ResultSet rs = stmt.getResultSet();
System.out.println("Test 3 ------------------------------------------------------");
int count = 0;
while (rs.next()) {
count++;
System.out.println(" GroupID: " + rs.getObject(1) + " FollowNoteID: " + rs.getObject(2) + " UserID: "
+ rs.getObject(3) + " NoteId: " + rs.getObject(4));
}
System.out.println("Test 3 ------------------------------------------------------");
Assert.assertEquals(2, count);
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test4() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT * FROM DP_GroupFollowNote WHERE (NoteClass = 1 OR (NoteClass = 4 AND UserID = ?)) AND NoteID = ? LIMIT ?, ?");
stmt.setInt(1, 0);
stmt.setInt(2, 7);
stmt.setInt(3, 1);
stmt.setInt(4, 10);
stmt.execute();
ResultSet rs = stmt.getResultSet();
System.out.println("Test 4 ------------------------------------------------------");
int count = 0;
while (rs.next()) {
count++;
System.out.println(" FollowNoteID: " + rs.getObject(1));
}
System.out.println("Test 4 ------------------------------------------------------");
Assert.assertEquals(1, count);
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test5() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT COUNT(FollowNoteID) FROM DP_GroupFollowNote WHERE (NoteClass = 1 OR (NoteClass = 4 AND UserID = ?)) AND NoteID = ?");
stmt.setInt(1, 0);
stmt.setInt(2, 7);
stmt.execute();
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
Assert.assertEquals(2, rs.getLong(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test6() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT * FROM DP_GroupFollowNote WHERE (NoteClass = 1 OR (NoteClass = 4 AND UserID = ?)) AND NoteID = ? AND UserID = ? LIMIT ?, ?");
stmt.setInt(1, 0);
stmt.setInt(2, 7);
stmt.setInt(3, 0);
stmt.setInt(4, 0);
stmt.setInt(5, 10);
stmt.execute();
ResultSet rs = stmt.getResultSet();
int count = 0;
System.out.println("Test 6 ------------------------------------------------------");
while (rs.next()) {
count++;
System.out.println("FollowNoteID: " + rs.getObject("FollowNoteID"));
}
System.out.println("Test 6 ------------------------------------------------------");
Assert.assertEquals(2, count);
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test7() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT COUNT(FollowNoteID) FROM DP_GroupFollowNote WHERE (NoteClass = 1 OR (NoteClass = 4 AND UserID = ?)) AND NoteID = ? AND UserID = ?");
stmt.setInt(1, 0);
stmt.setInt(2, 7);
stmt.setInt(3, 0);
stmt.execute();
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
Assert.assertEquals(2, rs.getLong(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test8() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("INSERT INTO DP_GroupFollowNote (NoteID, UserID, NoteClass, ADDTIME, UpdateTime, LastIP, DCashNumber) values(?,?,?,?,?,?,?)");
stmt.setInt(1, 8);
stmt.setInt(2, 8);
stmt.setInt(3, 1);
stmt.setTimestamp(4, new Timestamp(new Date().getTime()));
stmt.setTimestamp(5, new Timestamp(new Date().getTime()));
stmt.setString(6, "0.0.0.0");
stmt.setInt(7, 10);
stmt.execute();
Class.forName(getDriverName());
Connection conn2 = DriverManager.getConnection(getDBUrl());
Statement stmt2 = conn2.createStatement();
stmt2.execute("select count(*) from DP_GroupFollowNote_noteid0 where UserID = 8");
ResultSet rs = stmt2.getResultSet();
while (rs.next()) {
Assert.assertEquals(1, rs.getLong(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test9() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM DP_GroupFollowNote WHERE FollowNoteID = ?");
stmt.setInt(1, 140);
stmt.execute();
ResultSet rs = stmt.getResultSet();
int count = 0;
while (rs.next()) {
count++;
Assert.assertEquals(140, rs.getInt("FollowNoteID"));
}
Assert.assertEquals(1, count);
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test10() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT COUNT(FollowNoteID) FROM DP_GroupFollowNote WHERE (NoteClass = 1 OR (NoteClass = 4 AND UserID = ?)) AND NoteID = ? AND FollowNoteID <= ?");
stmt.setInt(1, 0);
stmt.setInt(2, 7);
stmt.setInt(3, 141);
stmt.execute();
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
Assert.assertEquals(1, rs.getLong(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test11() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT COUNT(DISTINCT(UserID)) FROM DP_GroupFollowNote WHERE NoteID = ?");
stmt.setInt(1, 7);
stmt.execute();
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
Assert.assertEquals(1, rs.getLong(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test12() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT COUNT(FollowNoteID) FROM DP_GroupFollowNote WHERE NoteID = ? AND UserID = ?");
stmt.setInt(1, 7);
stmt.setInt(2, 0);
stmt.execute();
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
Assert.assertEquals(3, rs.getLong(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test13() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT * FROM DP_GroupFollowNote WHERE NoteID = ? AND NoteClass = 1 ORDER BY FollowNoteID DESC LIMIT 1");
stmt.setInt(1, 7);
stmt.execute();
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
Assert.assertEquals(141, rs.getInt("FollowNoteID"));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test14() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT COUNT(FollowNoteID) F FROM DP_GroupFollowNote F INNER JOIN DP_GroupNote N ON F.NoteID = N.NoteID AND N.GroupID=? AND N.Status = 1 WHERE F.UserID = ? AND F.NoteClass = 1");
stmt.setInt(1, 100);
stmt.setInt(2, 0);
stmt.execute();
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
Assert.assertEquals(1, rs.getLong(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test15() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("UPDATE DP_GroupFollowNote SET DCashNumber = DCashNumber + ? WHERE FollowNoteID = ?");
stmt.setInt(1, 10);
stmt.setInt(2, 142);
stmt.execute();
Statement stmt2 = conn.createStatement();
stmt2.execute("select DCashNumber from DP_GroupFollowNote where FollowNoteID=142");
ResultSet rs = stmt2.getResultSet();
while (rs.next()) {
Assert.assertEquals(21, rs.getShort(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test16() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("SELECT DISTINCT GN.NoteID F FROM DP_GroupNote GN INNER JOIN DP_Group G ON GN.GroupID = G.GroupID AND G.Status = 0 INNER JOIN DP_GroupFollowNote GFN ON GN.NoteID = GFN.NoteID WHERE (GN.Status = 1 OR (GN.Status = 3 AND GN.UserID = ?)) AND GN.UserID <> ? AND GFN.UserID = ? AND GFN.NoteClass = 1");
stmt.setInt(1, 7);
stmt.setInt(2, 7);
stmt.setInt(3, 7);
stmt.execute();
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
Assert.assertEquals(0, rs.getLong(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test17() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("UPDATE DP_GroupFollowNote SET LastIP = ? WHERE FollowNoteID = ?");
stmt.setString(1, "5.1.1.1");
stmt.setInt(2, 142);
stmt.execute();
Statement stmt2 = conn.createStatement();
stmt2.execute("select LastIP from DP_GroupFollowNote where FollowNoteID=142");
ResultSet rs = stmt2.getResultSet();
while (rs.next()) {
Assert.assertEquals("5.1.1.1", rs.getString(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void test18() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn
.prepareStatement("UPDATE DP_GroupFollowNote SET NoteClass = ? WHERE FollowNoteID = ?");
stmt.setInt(1, 10);
stmt.setInt(2, 142);
stmt.execute();
Statement stmt2 = conn.createStatement();
stmt2.execute("select NoteClass from DP_GroupFollowNote where FollowNoteID=142");
ResultSet rs = stmt2.getResultSet();
while (rs.next()) {
Assert.assertEquals(10, rs.getInt(1));
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
}
