package hanjia;
//查询MySQL数据库中符合条件的学生信息、统计对应的学生数
//查询MySQL数据库实例
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class hanjia {
private Connection con;// 连接对象
public Connection getConnection(String dbname) {// 以数据库名为参数创建连接对象
String drivername = "com.mysql.jdbc.Driver";
// 数据库URL
String url = "jdbc:mysql://localhost:3306/" + dbname + "?characterEncoding=utf-8";//编码
String userName = "javase";// 登录数据库用户名
String password = "javase";// 用户密码
try {
// 加载MySQL驱动程序
Class.forName(drivername);
con = DriverManager.getConnection(url, userName, password);
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
public void getStudent(String dbname, String sql) {// 查询学生信息
try {
con = getConnection(dbname);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
System.out.println("学号" + "\t\t姓名" + "\t性别" + "\t出生日期" + "\t\t年龄" + "\t专业" + "\t\t\t宿舍");
while (rs.next()) {
String id = rs.getString("num");
String name = rs.getString("name");
String sex = rs.getString("gender");
String birthday = rs.getString("birthday");
int age = rs.getInt("age");
String major = rs.getString("major");
String dorm = rs.getString("dorm");
System.out.println(id + "\t" + name + "\t" + sex + "\t" + birthday + "\t" + age + "\t" + major + "\t" + dorm);
}
rs.close();
st.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void getRowCount(String dbname, String sql) {// 查询符合条件的记录数
try {
con = getConnection(dbname);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
if (rs.next()) {
System.out.println("符合条件的记录数有:" + rs.getInt(1) + "条");
} else {
System.out.println("没有符合条件的记录");
}
rs.close();
st.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
hanjia myjdbc = new hanjia();
String dbname = "computer_dept";
String sql1 = "SELECT * from students where major='计算机科学与技术'";
String sql2 = "SELECT count(*) from students where major='计算机科学与技术'";
myjdbc.getStudent(dbname, sql1);// 查询符合条件的学生
myjdbc.getRowCount(dbname, sql2);// 统计符合条件的记录数
}
}
- 文件-新建连接-自定义连接名-其他默认暂时不动-密码设置为root
2.双击打开连接-右键-导入数据库-
新建用户-
设置密码-访问权限
添加权限