程序设计代码 贴着玩。。 三

简单数据库操作

package JAVA实验;

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

class MyDB{
	public MyDB(){
		try{
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
			System.out.println("Load the embedded driver");
			Connection conn = DriverManager.getConnection("jdbc:derby:myDB;create=true");
			System.out.println("create and connect to myDB");
			conn.setAutoCommit(false);
			
			Statement s = conn.createStatement();//System.out.println("hello");
			s.execute("create table employee(num varchar(40), name varchar(40), sex varchar(40), salary float)");
			System.out.println("Created employee Table");
			s.execute("insert into employee values('1001', '张强', '男', 675.20)");
			s.execute("insert into employee values('1004', '李香', '女', 842.00)");
			s.execute("insert into employee values('1007', '王大山', '男', 756.00)");
			s.execute("insert into employee values('1010', '赵玉花', '女', 690.00)");
			s.close();
			System.out.println("close result set and statement");
			conn.commit();
			conn.close();
			try{
				DriverManager.getConnection("jdbc:derby:;shutdown=true");
			} catch(SQLException se) {
				System.out.println("Database shutdown normally");
			} 
		}catch(Throwable a){
			System.out.println("创建数据库出现异常");
		}
	}
	void show(Connection conn){
		try{
			Statement s = conn.createStatement();
			ResultSet rs1 = s.executeQuery("select num, name, sex, salary from employee order by salary");
			System.out.println("所有员工记录表，按薪水高低排序");
			System.out.println("num\t\tname\t\tsex\t\tsalary");
			while(rs1.next()) {
				StringBuilder builder = new StringBuilder(rs1.getString(1));
				builder.append("\t\t");
				builder.append(rs1.getString(2));
				builder.append("\t\t");
				builder.append(rs1.getString(3));
				builder.append("\t\t");
				builder.append(rs1.getFloat(4));
				System.out.println(builder.toString());
			}
			rs1.close();
			s.close();
		} catch(Throwable e) {
			System.out.println("Error in show!");
		}
	}
}

public class MyDBDemo {
	public static void main(String[] args){
		MyDB myDB = new MyDB();
		try{
			//首先连接到所创建的数据库
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
			Connection connection = DriverManager.getConnection("jdbc:derby:myDB;");
			connection.setAutoCommit(false);
			
			// 接下来的代码用来显示所有男员工的记录
			Statement s = connection.createStatement();
			ResultSet rs = s.executeQuery("SELECT num, name, salary FROM employee where sex='男'");
			System.out.println("男性员工的记录");
			System.out.println("num\t\tname\t\tsalary");
			while(rs.next()){
				StringBuilder builder = new StringBuilder(rs.getString(1));
				builder.append("\t\t");
				builder.append(rs.getString(2));
				builder.append("\t\t");
				builder.append(rs.getFloat(3));//System.out.println("mygod");
				System.out.println(builder.toString());
			}
		    // 接下来从数据库中增加一条数据记录：2001，邢雪花，女，650
			System.out.println("增加记录之前:");
			myDB.show(connection);//显示修改之前所有员工的记录
			s.execute("insert into employee values('2001', '邢雪花', '女', 650.00)");
			System.out.println("增加记录之后:");
			myDB.show(connection);//修改之后所有员工的记录
			//接下来将num值为2001的记录中的salary改为900
			s.execute("update employee set salary=900.00 where num='2001'" );
			System.out.println("修改数据之后：");
			myDB.show(connection);
			s.execute("drop table employee");
			s.close();
			rs.close();
			connection.commit();
			connection.close();	
			try{
				DriverManager.getConnection("jdbc:derby:;shutdown=true");
			} catch(SQLException se) {
				System.out.println("Database shutdown normally");
			}
		} catch(Throwable a){
			System.out.println("Error in main!");
		}
	}
}