package com.java.util;

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

public class MyDbUtil {
	public Connection conn = null;

	public Connection getConnection() {
		String username = "root";
		String password = "123456";
		String url = "jdbc:mysql://localhost:3308/no_forget";

		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username,
					password);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	public void closeConn(Connection conn, Statement stm, ResultSet rs) {
		if (rs != null)
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		if (stm != null)
			try {
				stm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		if (conn != null)
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	
	public Vector<Vector> getAllThings(){
		Vector <Vector>things=new Vector();
		Connection conn=null;
		Statement stm=null;
		ResultSet rs=null;
		conn=new MyDbUtil().getConnection();
		try {
			stm=conn.createStatement();
			rs=stm.executeQuery("select * from things");
			int i=1;   //行号
			while(rs.next()){
				Vector<String> v=new Vector<>();
				v.add(""+i++);
				v.add("NF"+rs.getInt("num")+"");
				v.add(rs.getString("title"));
				v.add(rs.getString("content"));
				v.add(rs.getString("time"));
				things.add(v);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return things;
	}
	
	public void addThing(int num,String title,String content,String time){
		Connection conn=null;
		PreparedStatement pstm=null;
		conn=new MyDbUtil().getConnection();
		try {
			if(num<0){
			pstm=conn.prepareStatement("insert into things values(default,?,?,?);");
			pstm.setString(1, title);
			pstm.setString(2, content);
			pstm.setString(3, time);
			}
			else{
				pstm=conn.prepareStatement("update things set title=?,"
						+ "content=?,time=? where num=?;");
				pstm.setString(1, title);
				pstm.setString(2, content);
				pstm.setString(3, time);
				pstm.setInt(4, num);
			}
			pstm.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public void deleteThing(int num){
		Connection conn=null;
		PreparedStatement pstm=null;
		conn=new MyDbUtil().getConnection();
		try {
				pstm=conn.prepareStatement("delete from things where"
						+ " num="+num);
			pstm.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		new MyDbUtil().addThing(-1,"title3", "内容", "测试的・");
		Vector<Vector> vv=new MyDbUtil().getAllThings();
		for (int i = 0; i < vv.size(); i++) {
			Vector<String> v=vv.get(i);
			for (int j = 0; j < v.size(); j++) {
				System.out.print(v.get(j)+"   ");
			}
			System.out.println();
		}
	}
}