java sqlserver操作类_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 list = new ArrayList();

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 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 queryStudent(int xsbs) {

// TODO Auto-generated method stub

ArrayList 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;

}