1.需求分析
在数据库学习当中，我接触到了一款新的数据库：SQL SERVER数据库；它与MYSQL相比有更强大的功能，而且连接
方式和MYSQL大同小异，今天就总结一下它的连接方法。
2.前期准备
(1)在连接本地sqlserver数据库之前，我们要先启动它的TCP/IP协议：
在sql server配置管理器中找到MSSQLSERVER的协议，打开TCP/IP协议
确认ip1的端口是否为1443；完成后通过鼠标右键启动TCP/IP协议。
(2)导入jar包
在项目中导入msbase.jar, mssqlserver.jar, msutil.jar
jar包下载网址https://mvnrepository.com/
3.连接代码
增：
//表单提交的数据
request.setCharacterEncoding("UTF-8");
String name= request.getParameter("name");
String num=request.getParameter("num");
String sex= request.getParameter("sex");
String birth= request.getParameter("birth");
String address= request.getParameter("address");
Connection connection =null;
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=student";//DatabaseName后是要访问的数据库名
String user="sa";
String password=""; //登录密码
connection= DriverManager.getConnection(url,user,password);
if(connection!=null)
{
//out.print("Success connect MySql server!");
}
else
{
out.print("fail connect MySql server!");
}
} catch (Exception e) {
out.print(" connect MySql server wrong!");
}
String sql = "insert into tablename(num,name,sex,birth,address) values(?,?,?,?,?)";//tablename为要访问的表名，value前面括号中是要插入的列名，后面是具体值
PreparedStatement ps = null;
boolean flag = false;
int a = 0;
//下面num,name,sex,birth,address为定义的变量，我的是通过上面表单提交获取的
ps = connection.prepareStatement(sql);
ps.setString(1, num);
ps.setString(2, name);
ps.setString(3, sex);
ps.setString(4, birth);
ps.setString(5, address);
a = ps.executeUpdate();//a用来判断操作是否成功，若a！=0则表示操作成功
删：
//与增相似，注解同上
request.setCharacterEncoding("UTF-8");
String id=request.getParameter("id");
Connection connection =null;
String db_url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=student";
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=student";
String user="sa";
String password="";
connection= DriverManager.getConnection(url,user,password);
if(connection!=null)
{
//out.print("Success connect MySql server!");
}
else
{
out.print("fail connect MySql server!");
}
} catch (Exception e) {
out.print(" connect MySql server wrong!");
}
String sql = "delete from students where id =" + id+ " ";
Statement stmt=connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
int a = 0;
a = stmt.executeUpdate(sql);
改：
//注解同上
request.setCharacterEncoding("UTF-8");
String id=request.getParameter("id");
String name= request.getParameter("name");
String num=request.getParameter("num");
String sex= request.getParameter("sex");
String birth= request.getParameter("birth");
String address= request.getParameter("address");
Connection connection =null;
String db_url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=student";
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=student";
String user="sa";
String password=""; //
connection= DriverManager.getConnection(url,user,password);
if(connection!=null)
{
//out.print("Success connect MySql server!");
}
else
{
out.print("fail connect MySql server!");
}
} catch (Exception e) {
out.print(" connect MySql server wrong!");
}
String sql="update students set num=‘" + num+ "‘,name=‘"+name+"‘,sex=‘"+sex+"‘,birth=‘"+birth+"‘,address=‘"+address+"‘ where id="+id+"";
Statement stmt=connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
int a=0;
a = stmt.executeUpdate(sql);
查：
//注解同上
Connection connection=null;
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String db_url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=student";
String user="sa";
String password=""; //
connection= DriverManager.getConnection(db_url,user,password);
if(connection!=null)
{
//out.print("Success connect MySql server!");
}
else
{
out.print("fail connect MySql server!");
}
} catch (Exception e) {
out.print(" connect MySql server wrong!");
}
Statement stmt=connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = null;
String sql = " select * from students";
rs = stmt.executeQuery(sql);
//此时我们获取的数据存在rs中我们可根据需求通过
while(rs.next())
{
/*
你需要的方法
*/
}
进一步操作数据
4.总结：
sqlserver数据库的操作与mysql有很大的相似之处，正是因为我对mysql数据库操作的熟练掌握才能让我在这次实验中
没有走弯路；所以初学某类事物时一定不要浅尝辄止。