java 本地sql_java代码连接本地SQL server数据库总结

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

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数据库操作的熟练掌握才能让我在这次实验中

没有走弯路；所以初学某类事物时一定不要浅尝辄止。