package org.wall.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SQLiteJDBC {
	public static Connection getConnection() {
		Connection c = null;
		try {
			Class.forName("org.sqlite.JDBC");
			c = DriverManager.getConnection("jdbc:sqlite:wall.db");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return c;
	}

	public static void saveOrUpdate(String sql) throws SQLException {
		Connection connection = getConnection();
		Statement createStatement = connection.createStatement();

		createStatement.executeUpdate(sql);

		createStatement.close();
		connection.close();
	}

	public static List<Map<String, String>> getData(int page) throws SQLException {
		int pageSize = 16;
		int offset = (page < 1 ? 1 : page) - 1 * pageSize;
		String sql = "select * from wall order by id limit 16 offset " + offset;

		List<Map<String, String>> result = new ArrayList<Map<String, String>>();
		Map<String, String> field = null;
		Connection connection = getConnection();
		Statement createStatement = connection.createStatement();

		ResultSet rs = createStatement.executeQuery(sql);
		while (rs.next()) {
			field = new HashMap<String, String>();
			field.put("id", rs.getString("id"));
			field.put("title", rs.getString("title"));
			field.put("descs", rs.getString("descs"));
			field.put("name", rs.getString("name"));
			result.add(field);
		}

		return result;
	}

	public static void delete() throws SQLException{
		Connection connection = getConnection();
		Statement createStatement = connection.createStatement();
		createStatement.execute("delete from  wall");
		
	}
	public static void main(String[] args) {
		String dropTable = "drop table wall";
		String createTable = "CREATE TABLE WALL (ID INT PRIMARY KEY  NOT NULL, TITLE  TEXT  NOT NULL,  DESCS  TEXT  NOT NULL,NAME TEXT NOT NULL)";
		try {
//			saveOrUpdate(dropTable);
			//saveOrUpdate(createTable);
			delete();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}