SqlServer数据库基础——使用JDBC对表增删改查

使用的IDE：Intellj idea 13.1

数据库中表的增删改查

1、连接SqlServer数据库

<span style="white-space:pre">		</span>static Connection conn;

<span style="white-space:pre">		</span>//static PreparedStatement st;

<span style="white-space:pre">		</span>static java.sql.Statement stat;

		String url = "jdbc:sqlserver://EE-LAB; DatabaseName=Fantastic FourMen";
		String name = "user";
		String passWord = "123456";

		//Connection con=null;
		//java.sql.Statement stat=null;
		//ResultSet rs=null;

		try {
			//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			System.out.println("驱动程序加载");
			System.out.println("即将连接到数据库");
		} catch (ClassNotFoundException e) {
			System.out.println("无法加载驱动程序" + e.getMessage());
			e.printStackTrace();
			return;
		}

		conn = DriverManager.getConnection(url, name, passWord);

		conn.close();

2、增加数据

	public static void insertData(String insertSql) {

		try {
			//String sql = Format("SET NOCOUNT ON "+ insertSql);
			stat = conn.createStatement();
			//ResultSet rs = stat.executeQuery(insertSql);
			int count = stat.executeUpdate(insertSql);  //用这个代替ResultSet rs = stat.executeQuery(insertSql); 同样可以插入
			System.out.println("成功插入" + count + "行");
			//rs.close();
			stat.close();

		} catch (SQLException e) {
			System.out.println("数据插入失败");
			e.printStackTrace();

		}

	}

3、删除数据

	private static void deleteData() {
		String deleteSql = "DELETE FROM new_table " + "WHERE ID = 5 ";
		try {
			stat = conn.createStatement();
			int count = stat.executeUpdate(deleteSql);
			stat.close();
			System.out.println("删除了" + count + "条数据");
		} catch (SQLException e) {
			System.out.println("数据删除失败");
			e.printStackTrace();
		}

	}

4、修改数据

	private static void upData() {
		String updateSql = "UPDATE testTable SET age = 5 WHERE name ='ling'";
		try {
			stat = conn.createStatement();
			int count = stat.executeUpdate(updateSql);
			stat.close();
		} catch (SQLException e) {
			System.out.println("数据修改失败");
			e.printStackTrace();
		}
	}

5、查询数据

	public static void serchData(String serchSql) {
		try {
			stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(serchSql);
			System.out.println("查询结果：");
			while (rs.next()) {
				System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4));
			}
			rs.close();
			stat.close();


		} catch (SQLException e) {
			System.out.println("查询失败");
			e.printStackTrace();
		}

	}

6、创建新表，并添加另外一个表的数据

	private static void createInsertNewTable() {
		String serchSql = "select * from new_table";//创建新表
		String insertSql = "insert into new_table" + " select name,age,sex " + "from testTable";//将testTable表中的数据插入到新表中
		createTable("new_table");  //创建一张新表
		insertData(insertSql); //插入数据
		serchData(serchSql);  //查找数据
	}

	private static void createTable(String tableName) {
		String createTableSql = "create table " + tableName + " (ID int primary key ,name ntext,age int,sex nchar(10))";
		//System.out.println(createTableSql);
		try {
			stat = conn.createStatement();
			int count = stat.executeUpdate(createTableSql);
			stat.close();
			//ResultSet rs = stat.executeQuery(createTableSql);
			//rs.close();
		} catch (SQLException e) {
			System.out.println(tableName + "创建失败");
			e.printStackTrace();
		}
	}