jdbcconsole.java

java编码：JavaCode_version01.rar

/**
 * 
 */
package com.waytojob.teaching.jdbc;

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

import javax.swing.JOptionPane;

/**
 * @author Administrator
 *
 */
public class JdbcConsole
{
	private final static String JDBC_DRIVER_NAME="sun.jdbc.odbc.JdbcOdbcDriver";
	private final static String JDBC_URL="jdbc:odbc:waytojob";
	private final static String JDBC_USER_NAME="";
	private final static String JDBC_PASSWORD="";
	private static boolean isDriverOk=true;

	static
	{
		try
		{
			Class.forName(JDBC_DRIVER_NAME);
		}
		catch (ClassNotFoundException e)
		{
			JdbcConsole.isDriverOk=false;
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
	}
	/**
	 * 
	 */
	public JdbcConsole()
	{
		super();
		// TODO 自动生成构造函数存根
	}

	/**
	 * @param args
	 */
	public static void main(String[] args)
	{
		JdbcConsole console = new JdbcConsole();
		while (true)
		{
			String command = null;
			do
			{
				command = JOptionPane
						.showInputDialog("请输入命令[Create(创建表)|Add(添加记录)|Query(查询记录)|Quit(退出)");
			} while (command == null);
			if (command.equalsIgnoreCase("quit"))
			{
				break;
			}
			else if (command.equalsIgnoreCase("create"))
			{
				Table table = console.getTableInfo();
				if (table != null)
				{
					console.createTable(table);
				}
			}
			else if(command.equalsIgnoreCase("add"))
			{
				Student std=console.getStudentInfo();
				console.addStudent(std);
			}
			else if(command.equalsIgnoreCase("query"))
			{
				console.doQuery();
			}
			else
			{
				JOptionPane.showMessageDialog(null,"无法识别的命令");
			}
		}// end while
		System.exit(0);
	}
	private Table getTableInfo()
	{
		String tableName = null;
		while (tableName == null
				|| (tableName = tableName.trim()).length() == 0)
			tableName = JOptionPane.showInputDialog("请输入表的名称");
		StringBuffer sb = new StringBuffer();
		String input;
		boolean bMatched;// 字段名和字段值是否匹配
		do
		{
			input = null;
			bMatched = true;
			while (input == null || (input = input.trim()).length() == 0)
			{
				input = JOptionPane.showInputDialog("请输入字段名称:");
			}
			if (input.equalsIgnoreCase("q"))
			{
				continue;
			}
			if (sb.length() > 0)
				sb.append(",");
			sb.append(input + " ");
			input = null;
			while (input == null || (input = input.trim()).length() == 0)
			{
				input = JOptionPane.showInputDialog("请输入字段的数据类型和长度[类型名(长度)]：");
			}
			if (input.equalsIgnoreCase("q"))
			{
				bMatched = false;
				continue;
			}
			sb.append(input);
			bMatched = true;
		} while (!input.equalsIgnoreCase("q"));
		if (!(bMatched && sb.length() > 0))
			return null;
		return new Table(tableName, sb.toString());
	}
	private void createTable(Table table)
	{
		if (table == null)
			return;
		if(!JdbcConsole.isDriverOk)
		{
			System.out.println("请检查JDBC驱动器配置");
			return;
		}
		// 获取数据库连接
		Connection conn = null;
		Statement stmt = null;
		try
		{
			//JDBC_ODBC驱动器已经加载
			//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			conn = DriverManager.getConnection(JdbcConsole.JDBC_URL, JdbcConsole.JDBC_USER_NAME,
					JdbcConsole.JDBC_PASSWORD);
			String sqlCommand = "CREATE TABLE " + table.getName() + "("
					+ table.getFields() + ")";
			stmt = conn.createStatement();
			stmt.executeUpdate(sqlCommand);
		}
		catch (SQLException e)
		{
			System.out.println("创建数据库表出错:" + e.toString());
		}
		finally
		{
			if (conn != null)
			{
				try
				{
					conn.close();
				}
				catch (SQLException ex)
				{
					System.out.println("无法关闭数据库连接：" + ex.toString());
				}
			}
		}
	}

	private Student getStudentInfo()
	{
		String[] prompts = { "学号", "姓名", "性别", "手机", "电子邮件", "编程语言", "级别", "说明" };
		Student std = new Student();
		String input = null;
		for (int i = 0; i < 8; i++)
		{
			do
			{
				input = JOptionPane.showInputDialog("请输入" + prompts[i]);
			} while (input == null);
			switch (i)
			{
				case 0:
					std.setId(input);
					break;
				case 1:
					std.setName(input);
					break;
				case 2:
					std.setGendar(input);
					break;
				case 3:
					std.setMobile(input);
					break;
				case 4:
					std.setEmail(input);
					break;
				case 5:
					std.setLanguage(input);
					break;
				case 6:
					std.setLevel(input);
					break;
				case 7:
					std.setRemark(input);
					break;
			}
		}
		return std;
	}

	private void addStudent(Student std)
	{
		if (std == null)
		{
			return;
		}
		if(!JdbcConsole.isDriverOk)
		{
			System.out.println("请检查JDBC驱动器配置");
			return;
		}
		Connection conn=null;
		try
		{
			//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			conn= DriverManager.getConnection(JdbcConsole.JDBC_URL,
					JdbcConsole.JDBC_USER_NAME,
					JdbcConsole.JDBC_PASSWORD);
			String sqlCommand = "INSERT INTO students VALUES('" + std.getId() + "','"
					+ std.getName() + "','" + std.getGendar() + "','"
					+ std.getMobile() + "','" + std.getEmail() + "','"
					+ std.getLanguage() + "','"
					+ std.getLevel() + "','" + std.getRemark() + "')";
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sqlCommand);
		}
		catch (SQLException e)
		{
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		finally
		{
			if(conn != null)
			{
				try
				{
					conn.close();
				}
				catch (SQLException e)
				{
					// TODO 自动生成 catch 块
					e.printStackTrace();
				}
			}
		}
	}
	private void doQuery()
	{
		if(!JdbcConsole.isDriverOk)
		{
			System.out.println("请检查JDBC驱动器配置");
			return;
		}
		Connection conn=null;
		try
		{
			//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			conn= DriverManager.getConnection(JdbcConsole.JDBC_URL,
					JdbcConsole.JDBC_USER_NAME,
					JdbcConsole.JDBC_PASSWORD);
			String sql="select * from students";
			Statement stmt = conn.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			StringBuffer sb=new StringBuffer();
			while(rs.next())
			{
				for(int i=0;i<8;i++)
				{
					if(i>0)
					{
						sb.append("|");
					}
					sb.append(rs.getString(i+1).trim());
				}
				sb.append("\n");
			}
			JOptionPane.showMessageDialog(null,"查询结果如下：\n"+
					sb.toString());
		}
		catch (SQLException e)
		{
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		finally
		{
			if(conn != null)
			{
				try
				{
					conn.close();
				}
				catch (SQLException e)
				{
					// TODO 自动生成 catch 块
					e.printStackTrace();
				}
			}
		}
	}
}