java操作SQLserver数据库
假设已经用SQL server建好了数据库。且新建了登录名。
现在用java连接，进行数据库的增删改查操作。以学生管理系统为例。
1、通过接口ConnectionProperty连接数据库：
package JDBC;
interface ConnectionProperty {
// 10.177.7.203是IPv4 SQLEXPRESS:1433是端口号 databaseName=pxscj 是数据库名称
String url = "jdbc:sqlserver://10.183.37.143:1433;databasename=StudentsSystemDemo";
//设置用户名和密码，与数据库登录时的保持一致
String user = "hj";
String password = "123";
}
2、插入操作：插入学生信息
public static void insertStudents() {
// TODO Auto-generated method stub
System.out.println();
System.out.println("请按照格式输入学生的信息："+"学号，姓名，性别，出生日期，专业，学分，备注");
ArrayList<String> list = new ArrayList<String>();
for (int i = 0; i <=6; i++) {
list.add(input.next());
}
//实例化Students类，从list中获取对应的值
Students student = new Students(list.get(0),list.get(1),Integer.valueOf(list.get(2)),list.get(3),list.get(4),Integer.valueOf(list.get(5)),list.get(6));
//写sql语句，用通配符代替相应的值
String sql = "INSERT INTO Students(SNo,SName,SSex,SBirthDate,SMajority,SScore,SPS) "
+ "VALUES(?, ?, ?, ?, ?, ?, ?)";
//使用DriverManager连接到数据库
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setString(1, student.getSNo()); ///1、2....7依次对应sql语句中的7个通配符。注意:从1开始
pstmt.setString(2, student.getSName());
pstmt.setInt(3, student.getSSex());
pstmt.setString(4, student.getSBirthDate());
pstmt.setString(5, student.getSMajority());
pstmt.setInt(6, student.getSScore());
pstmt.setString(7, student.getSPS());
//更新
int rowCount = pstmt.executeUpdate();
System.out.println("插入" + rowCount + "行");
} catch (SQLException e) {
e.printStackTrace();
}
}
3、删除学生信息
public static void deleteStudents() {
// TODO Auto-generated method stub
System.out.println();
System.out.println("请按照格式输入学生的信息："+"学号"); //设置，按照学号删除
Students student = new Students(input.next()); //在Students中建立一个仅含有变量'学号'的构造器
String sql = "DELETE FROM Students WHERE SNo=?"; //sql语句
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setString(1, student.getSNo());
int rowCount = pstmt.executeUpdate();
System.out.println("删除" + rowCount + "行");
} catch (SQLException e) {
e.printStackTrace();
}
}
4、 修改学生信息：
public static void editStudents() {
// TODO Auto-generated method stub
System.out.println();
System.out.println("请输入要修改的学生学分、学号");
Students student = new Students();
student.setSScore(input.nextInt());
student.setSNo(input.next());
String sql = "UPDATE Students set SScore=? WHERE SNo=?";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setInt(1, student.getSScore());
pstmt.setString(2, student.getSNo());
int rowCount = pstmt.executeUpdate();
System.out.println("修改" + rowCount + "行");
} catch (SQLException e) {
e.printStackTrace();
}
}
5、查询学生信息：查询稍长一些，查询出结果要显示出来
//用来显示结果的方法
public static void searchStudents() {
// TODO Auto-generated method stub
System.out.println();
System.out.println("学号\t\t姓名\t\t性别\t\t出生日期\t\t专业\t\t\t总学分\t\t\t备注");
ArrayList<Students> xsbs = queryStudent(1);///按照性别查询，1代表男性，0是女性。也可以自己设置其他查询条件
for (int i = 0; i < xsbs.size(); i++) {
System.out.print(xsbs.get(i).getSNo() + "\t");
System.out.print(xsbs.get(i).getSName() + "\t");
System.out.print(xsbs.get(i).getSSex() + "\t\t");
System.out.print(xsbs.get(i).getSBirthDate() + "\t");
System.out.print(xsbs.get(i).getSMajority() + "\t\t");
System.out.print(xsbs.get(i).getSScore() + "\t\t\t");
System.out.println(xsbs.get(i).getSPS());
}
}
//用来查询的方法
public static ArrayList<Students> queryStudent(int xsbs) {
// TODO Auto-generated method stub
ArrayList<Students> showList = new ArrayList<>();
String sql = "SELECT SNo, SName, SSex, SBirthDate,SMajority, SScore, SPS "
+ "FROM Students "
+ "WHERE SSex = ? "
+ "ORDER BY SNo";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setInt(1, xsbs );
ResultSet rs = pstmt.executeQuery(); //定义一个结果集，存放查询结果
Students student;
while (rs.next()) {
student = new Students();
student.setSNo(rs.getString("SNo"));
student.setSName(rs.getString("SName"));
student.setSSex(rs.getInt("SSex"));
student.setSBirthDate(rs.getString("SBirthDate"));
student.setSMajority(rs.getString("SMajority"));
student.setSScore(rs.getInt("SScore"));
student.setSPS(rs.getString("SPS"));
showList.add(student); //去除结果集中的项并放入， showlist数组中
}
} catch (SQLException e) {
e.printStackTrace();
}
return showList;
}
